Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
MalcolmCICWF
Creator III
Creator III

Easiest way to Group based on a Max Date

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

1 Solution

Accepted Solutions
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;

View solution in original post

8 Replies
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

MalcolmCICWF
Creator III
Creator III
Author

The PoolID and ErcDate are together as the GroupID,

I will correct the snapshot of the data

sunny_talwar

I guess the idea is to use FirstSortedValue() for all fields except GroupID and CreatedDate. GroupID because that is being grouped by and CreatedDate because that we can find the max of.

It would be easier to provide working script, if you can provide your script as text instead of image. Difficult to help when the script is image.

MalcolmCICWF
Creator III
Creator III
Author

Iv'e never used that function as of yet. I did see it in one of the threads but I couldn't figure out how they explained it.

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;

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;

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;

jonas_rezende
Specialist
Specialist

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;

MalcolmCICWF
Creator III
Creator III
Author

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;