Need help with Peek or Previous function and for loop to get output data set.

[shrikantm]

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.

[shrikantm]

Anyone can assist with above? Thank you.

[sunny_talwar · Accepted solution]

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;