Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
shrikantm
Partner - Contributor
Partner - Contributor

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

Hi Experts,

I need the Qlik script which will give me below output for following data-set.

Data Set -

Load * Inline [

KeyRptDate (M/D/YYYY)Maturity Date (M/D/YYYY)
A6/1/20184/1/2018
A7/1/201810/1/2018
A8/1/201810/1/2018
A9/1/201810/1/2018
A10/1/201812/1/2018
B6/1/20181/1/2019
B7/1/20181/1/2019
B8/1/20181/1/2019
B9/1/20181/1/2019
B10/1/20181/1/2019
C6/1/20183/1/2018
C7/1/20188/1/2018
C8/1/20189/1/2018
C9/1/201810/1/2018
C10/1/201810/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 -

 

KeyRptDateMaturity DateLast Amended Date (Old Maturity Date)
A6/1/20184/1/20184/1/2018
A7/1/201810/1/20184/1/2018
A8/1/201810/1/20184/1/2018
A9/1/201810/1/20184/1/2018
A10/1/201812/1/201810/1/2018
B6/1/20181/1/20191/1/2019
B7/1/20181/1/20191/1/2019
B8/1/20181/1/20191/1/2019
B9/1/20181/1/20191/1/2019
B10/1/20181/1/20191/1/2019
C6/1/20183/1/20183/1/2018
C7/1/20188/1/20183/1/2018
C8/1/20189/1/20188/1/2018
C9/1/201810/1/20189/1/2018
C10/1/201810/1/20189/1/2018

Thank you.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
shrikantm
Partner - Contributor
Partner - Contributor
Author

Anyone can assist with above? Thank you.

sunny_talwar

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;