Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I need the Qlik script which will give me below output for following data-set.
Data Set -
Load * Inline [
Key | RptDate (M/D/YYYY) | Maturity Date (M/D/YYYY) |
A | 6/1/2018 | 4/1/2018 |
A | 7/1/2018 | 10/1/2018 |
A | 8/1/2018 | 10/1/2018 |
A | 9/1/2018 | 10/1/2018 |
A | 10/1/2018 | 12/1/2018 |
B | 6/1/2018 | 1/1/2019 |
B | 7/1/2018 | 1/1/2019 |
B | 8/1/2018 | 1/1/2019 |
B | 9/1/2018 | 1/1/2019 |
B | 10/1/2018 | 1/1/2019 |
C | 6/1/2018 | 3/1/2018 |
C | 7/1/2018 | 8/1/2018 |
C | 8/1/2018 | 9/1/2018 |
C | 9/1/2018 | 10/1/2018 |
C | 10/1/2018 | 10/1/2018 |
Logic required - in following month if there is no change in Maturity Date to previous then original maturity date is taken as last amended date, else if there is a change in Maturity date in following month, then previous Maturity Date becomes the last amended date.
Output Needed -
Key | RptDate | Maturity Date | Last Amended Date (Old Maturity Date) |
A | 6/1/2018 | 4/1/2018 | 4/1/2018 |
A | 7/1/2018 | 10/1/2018 | 4/1/2018 |
A | 8/1/2018 | 10/1/2018 | 4/1/2018 |
A | 9/1/2018 | 10/1/2018 | 4/1/2018 |
A | 10/1/2018 | 12/1/2018 | 10/1/2018 |
B | 6/1/2018 | 1/1/2019 | 1/1/2019 |
B | 7/1/2018 | 1/1/2019 | 1/1/2019 |
B | 8/1/2018 | 1/1/2019 | 1/1/2019 |
B | 9/1/2018 | 1/1/2019 | 1/1/2019 |
B | 10/1/2018 | 1/1/2019 | 1/1/2019 |
C | 6/1/2018 | 3/1/2018 | 3/1/2018 |
C | 7/1/2018 | 8/1/2018 | 3/1/2018 |
C | 8/1/2018 | 9/1/2018 | 8/1/2018 |
C | 9/1/2018 | 10/1/2018 | 9/1/2018 |
C | 10/1/2018 | 10/1/2018 | 9/1/2018 |
Thank you.
Try this
Table: LOAD * INLINE [ Key, RptDate, MaturityDate A, 6/1/2018, 4/1/2018 A, 7/1/2018, 10/1/2018 A, 8/1/2018, 10/1/2018 A, 9/1/2018, 10/1/2018 A, 10/1/2018, 12/1/2018 B, 6/1/2018, 1/1/2019 B, 7/1/2018, 1/1/2019 B, 8/1/2018, 1/1/2019 B, 9/1/2018, 1/1/2019 B, 10/1/2018, 1/1/2019 C, 6/1/2018, 3/1/2018 C, 7/1/2018, 8/1/2018 C, 8/1/2018, 9/1/2018 C, 9/1/2018, 10/1/2018 C, 10/1/2018, 10/1/2018 ]; FinalTable: LOAD *, If(Key = Previous(Key), If(MaturityDate = Previous(MaturityDate), Peek('LastAmendedDate'), Peek('MaturityDate')), MaturityDate) as LastAmendedDate Resident Table Order By Key, RptDate; DROP Table Table;
Anyone can assist with above? Thank you.
Try this
Table: LOAD * INLINE [ Key, RptDate, MaturityDate A, 6/1/2018, 4/1/2018 A, 7/1/2018, 10/1/2018 A, 8/1/2018, 10/1/2018 A, 9/1/2018, 10/1/2018 A, 10/1/2018, 12/1/2018 B, 6/1/2018, 1/1/2019 B, 7/1/2018, 1/1/2019 B, 8/1/2018, 1/1/2019 B, 9/1/2018, 1/1/2019 B, 10/1/2018, 1/1/2019 C, 6/1/2018, 3/1/2018 C, 7/1/2018, 8/1/2018 C, 8/1/2018, 9/1/2018 C, 9/1/2018, 10/1/2018 C, 10/1/2018, 10/1/2018 ]; FinalTable: LOAD *, If(Key = Previous(Key), If(MaturityDate = Previous(MaturityDate), Peek('LastAmendedDate'), Peek('MaturityDate')), MaturityDate) as LastAmendedDate Resident Table Order By Key, RptDate; DROP Table Table;