Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

MalcolmCICWF
Contributor II

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

Re: Easiest way to Group based on a Max Date

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;

8 Replies

Re: Easiest way to Group based on a Max Date

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
Contributor II

Re: Easiest way to Group based on a Max Date

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

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
Contributor II

Re: Easiest way to Group based on a Max Date

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

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

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
Valued Contributor

Re: Easiest way to Group based on a Max Date

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
Contributor II

Re: Easiest way to Group based on a Max Date

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;