Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to get the amount on max date only into another table by each ID using qliksense script.
Below is the dataset and expected result .
Thanks In advance
Hi, you can use a sorted load by Id and date descendant, and use peek to check when the id changes; with this you can set a flag on the first records of each id (being date descendant it will be the higher date), and use this flag to filter the records.
OriginalData:
LOAD ID, Date_1, Date_2, Amount
From...;
FlaggedData:
LOAD ID, Date_1,
If(ID<>Peek(ID),1,0) as isMaxDate
Resident OriginalData
Order By ID, Date_1 desc;
You can do an inner join to reduce the date to only the max dates or a left join to just add the flag to all the data.
Hi, you can use a sorted load by Id and date descendant, and use peek to check when the id changes; with this you can set a flag on the first records of each id (being date descendant it will be the higher date), and use this flag to filter the records.
OriginalData:
LOAD ID, Date_1, Date_2, Amount
From...;
FlaggedData:
LOAD ID, Date_1,
If(ID<>Peek(ID),1,0) as isMaxDate
Resident OriginalData
Order By ID, Date_1 desc;
You can do an inner join to reduce the date to only the max dates or a left join to just add the flag to all the data.