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:
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_INVWhere @FILTER@
Thanks in advance!
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@)
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.