1 Reply Latest reply: Oct 25, 2017 4:23 PM by Stefan Wühl RSS

    Multiple Min Dates

    Afroz Hyder

      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!

        • Re: Multiple Min Dates
          Stefan Wühl

          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;