Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Multiple Min Dates

DATA:

      

ACCOUNT_IDLOG_DATETYPESUCCESSCREDIT_OR_DEBITAMOUNT
12-JanACC1
23-JanBCC5
34-JanBCC45
45-JanBCC43
56-JanCC624
67-JanCCC453
78-JanACC7
89-JanACC48676
910-JanACC486
1011-JanACC5
115-JanCC4864
16-JanBCC4
27-JanCCC4
38-JanCCC45
49-JanCCC486
517-JanCCC46
618-JanCC467
719-JanACC64
85-JanACC5
96-JanCC245
107-JanCC6
118-JanBCC45
239-JanBCC465
525-JanCC1

Can someone explain why this doesnt work? Please

     MINDATES:

        LOAD

            ACT,

            FLOOR(MIN(DATE)) as FIRST_DATE,

            IF(MATCH(UPPER(TYPE), 'A'), FLOOR(MIN(DATE))) as A_MIN_DATE,

            IF(MATCH(UPPER(TYPE), 'B', 'C'),  FLOOR(MIN(DATE))) as BC_MIN_DATE

        FROM AttachedFile

        WHERE MATCH(UPPER(SUCCESS), 'C','Y')

        GROUP BY ACT;

       

I want to get min date for each scenario. Please help!

1 Reply
swuehl
MVP
MVP

You need to use an aggregation function on all fields in your LOAD not listed in the group by.

    MINDATES:

        LOAD

            ACT,

            FLOOR(MIN(DATE)) as FIRST_DATE,

           FLOOR(MIN( IF(MATCH(UPPER(TYPE), 'A'), DATE))) as A_MIN_DATE,

            FLOOR(MIN( IF(MATCH(UPPER(TYPE), 'B', 'C'),DATE))) as BC_MIN_DATE

        FROM AttachedFile

        WHERE MATCH(UPPER(SUCCESS), 'C','Y')

        GROUP BY ACT;