topic Re: Easiest way to Group based on a Max Date in QlikView

Easiest way to Group based on a Max Date

MalcolmCICWF — Tue, 26 Apr 2016 20:19:09 GMT

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:

Re: Easiest way to Group based on a Max Date

sunny_talwar — Tue, 26 Apr 2016 20:21:31 GMT

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

MalcolmCICWF — Tue, 26 Apr 2016 20:47:03 GMT

The PoolID and ErcDate are together as the GroupID,

I will correct the snapshot of the data

Re: Easiest way to Group based on a Max Date

sunny_talwar — Tue, 26 Apr 2016 20:50:17 GMT

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.

Re: Easiest way to Group based on a Max Date

MalcolmCICWF — Tue, 26 Apr 2016 20:54:37 GMT

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;

Re: Easiest way to Group based on a Max Date

sunny_talwar — Tue, 26 Apr 2016 20:55:00 GMT

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 — Tue, 26 Apr 2016 20:56:41 GMT

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_rezende — Wed, 27 Apr 2016 12:34:32 GMT

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

MalcolmCICWF — Wed, 27 Apr 2016 16:10:51 GMT

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;