8 Replies Latest reply: Apr 27, 2016 12:10 PM by Jeremy Schmitt RSS

    Easiest way to Group based on a Max Date

    Jeremy Schmitt

      I have not worked with groups a lot within Qlikview and I am receiving an "invalid expression" error when running the script. I found this script via other  community posts, but cannot get it to work. I was hoping the community could help me out on this one.

       

      I have a GoupID which can have multiple records in the table, but I only want the most recent one which is determined by the CreateDate.

       

      Criteria_Table2:

      /*Grab the most recent ErcDate grouped by the GroupID*/

      LOAD

        PoolPlusId,

        ErcAmt,

        ErcDate,

        Max(CreateDate) as MaxCreateDate,

        ErcInd,

        ErcSnapshot,

        ErcSnapshotName,

        BusinessLine,

        Client_Major,

        PoolStart,

        GroupID

         

      Resident Criteria_Table1

       

      Group By GroupID;

       

      Drop Table Criteria_Table1;

       

       

      SAMPLE OF THE DATA:

      Capture.JPG

        • Re: Easiest way to Group based on a Max Date
          Sunny Talwar

          Use FirstSortedValue() on each of the fields.

           

          LOAD PoolID,

                    FirstSortedValue(ErcDate, -CreateDate) AS ErcDate,

                    ...

          FROM Source

          Group By PoolID;

           

          or you can do a right join to keep only the max createdate values

          • Re: Easiest way to Group based on a Max Date
            Sunny Talwar

            Try this:

             

            Criteria_Table2:

            LOAD

              FirstSortedValue(PoolPlusId, -CreateDate) as PoolPlusId

              FirstSortedValue(ErcAmt, -CreateDate) as ErcAmt

              FirstSortedValue(ErcDate, -CreateDate) as ErcDate

              Max(CreateDate) as MaxCreateDate,

              FirstSortedValue(ErcInd, -CreateDate) as ErcInd

              FirstSortedValue(ErcSnapshot, -CreateDate) as ErcSnapshot

              FirstSortedValue(ErcSnapshotName, -CreateDate) as ErcSnapshotName

              FirstSortedValue(BusinessLine, -CreateDate) as BusinessLine

              FirstSortedValue(Client_Major, -CreateDate) as Client_Major

              FirstSortedValue(PoolStart, -CreateDate) as PoolStart

              GroupID

            Resident Criteria_Table1

            Group By GroupID;

             

            Drop Table Criteria_Table1;

            • Re: Easiest way to Group based on a Max Date
              Sunny Talwar

              Option 2:

               

              Criteria_Table2:

              LOAD

                PoolPlusId,

                ErcAmt,

                ErcDate,

                CreateDate,

                ErcInd,

                ErcSnapshot,

                ErcSnapshotName,

                BusinessLine,

                Client_Major,

                PoolStart,

                GroupID  

              Resident Criteria_Table1;

               

              Right Join (Criteria_Table2)

              LOAD GroupID,

                        Max(CreateDate) as CreateDate

              Resident Criteria_Table2

              Group By GroupID;

               

              Drop Table Criteria_Table1;

              • Re: Easiest way to Group based on a Max Date
                Jonas Melo

                Hi, a jschmitt.

                 

                See if the code below help:

                 

                Max_Criterea_Table2:

                Load

                MaxString(CreateDate) as MaxCreateDate

                From Criteria_Table1;


                NoConcatenate


                Criteria_Table2:

                LOAD

                  PoolPlusId,

                  ErcAmt,

                  ErcDate,

                  CreateDate

                  ErcInd,

                  ErcSnapshot,

                  ErcSnapshotName,

                  BusinessLine,

                  Client_Major,

                  PoolStart,

                  GroupID

                   

                Resident Criteria_Table1

                Where

                Exists(MaxCreateDate);

                 

                Drop Tables

                Criteria_Table1,

                Max_Criterea_Table2;

                • Re: Easiest way to Group based on a Max Date
                  Jeremy Schmitt

                  Thanks, I actually went with the join version based of testing the script. I changed it up a little bit though. THanks for the info, I never considered it this way, Very helpful.

                   

                  ERC_Table2:

                  /*Grab the most recent ErcDate grouped by the GroupID*/

                   

                   

                  Load

                    GroupID,

                    Max(CreateDate) as CreateDate

                   

                  Resident ERC_Table1

                   

                  Group By GroupID;

                   

                   

                  ////////////////////////

                  Left Join (ERC_Table2)

                  ////////////////////////

                   

                   

                  LOAD

                    *

                     

                  Resident ERC_Table1;

                   

                  Drop Table ERC_Table1;