Hi
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 | LeaseID | 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 in advance.
Regards
Ankhi