Clarity

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

Hi Greg.baker.1 

Issue is that your hidden key is Investment ID which is unique for each C_SLOTTED_DATE. Since both of these columns are present so distinct will not work as you are expecting.

I will suggest, if you can change your hidden key (@Select:ID:PRJ_ID@)

Regards,

Prashank Singh

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.

I would disagree with this ; it depends what your lookup is designed to do ; for sure if you need to identify a single investment then you would need to return the investment id, but if the lookup only exists to identify a specific quarter then it should return something unique about that quarter (which you then use in your downstream logic) - eg. your 'quarter_display' column.