Get value on max date by ID

[Rsaiq]

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

[rubenmarin · Accepted solution]

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.