AnsweredAssumed Answered

Distinct Lookups

Question asked by Greg.baker.1 on Jul 17, 2017
Latest reply on Jul 18, 2017 by Dave_3.0

Hi - I have created a case statement within a Lookup for a date field in one of our portlets (have it setup to read YYYY, Q, Quarter#), and I am attempting to make it distinct.

 

When looking at lookups I know I need to bring in the hidden_key which makes sense, but when trying to do distinct on this, it actually does the distinct on the hidden_key as well.

 

Is there a way to make the distinct statement only apply to the Quarter?

 

My Values are coming in as:

2017 Q1

2017 Q1

2017 Q1

2017 Q2

2017 Q2

2017 Q2

Instead of:

2017 Q1

2017 Q2

 

SELECT DISTINCT  @Select:ID:PRJ_ID@,       
        @SELECT:Case when(C_SLOTTED_DATE is not null)
       then Concat(Concat(TO_CHAR(C_SLOTTED_DATE,'YYYY'),' Q'), TO_CHAR(C_SLOTTED_DATE,'Q')) else null end:Quarter_Display@
From ODF_CA_INV
Where @FILTER@

 

Thanks in advance!

-Greg

Outcomes