topic Re: Need help with Peek or Previous function and for loop to get output data set. in QlikView

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

shrikantm — Thu, 04 Oct 2018 14:37:56 GMT

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.

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

shrikantm — Mon, 15 Apr 2019 18:15:03 GMT

Anyone can assist with above? Thank you.

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

sunny_talwar — Mon, 15 Apr 2019 18:25:16 GMT

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;