Easiest way to Group based on a Max Date

[MalcolmCICWF]

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:

[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]

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]

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 · Accepted solution]

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]

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]

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;