Clarity

Expand all | Collapse all

Distinct Lookups

  • 1.  Distinct Lookups

    Posted 07-17-2017 05:47 PM

    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



  • 2.  Re: Distinct Lookups

    Posted 07-18-2017 01:00 AM

    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



  • 3.  Re: Distinct Lookups

    Posted 07-18-2017 04:10 AM

    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.