Discussion Board for collaboration related to QlikView App Development.
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:
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;
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
The PoolID and ErcDate are together as the GroupID,
I will correct the snapshot of the data
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.
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;
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;
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;
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;
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;