Select a row based on max value of another Column

Ankhi — Wed, 07 Jul 2021 18:37:11 GMT

I have a data like below, this same pattern is repeated for multiple LeaseIDs with different Periods. For some cases , like Mar-20 here, there are two lines against the same Type = Cash. In such cases I would only like to pick up the ones with maximum header id i.e Mar-20 , LeaseID=L1 ,Type= Cash should have 350 only .

Can you please help me create a set analysis expression or load script in Qlikview which will only load filtered data based on max header id as well as Period, Lease Id and Type and Amt.

Header Id	Line Id	LID	Type	Period	Amt
100	11	L1	Cash	Jun-21	100
101	12	L1	Amort	May-21	200
102	13	L1	Interest	Apr-21	300
100	14	L1	Cash	Jun-20	100
101	15	L1	Amort	May-20	200
102	16	L1	Interest	Apr-20	300
100	17	L1	Cash	Mar-20	250
120	18	L1	Cash	Mar-20	350
101	19	L1	Amort	Mar-20	500
102	20	L1	Interest	Mar-20	430

Thanks

Ankhi

topic Select a row based on max value of another Column in App Development

Select a row based on max value of another Column