LEAD(...) OVER (PARTITION BY ... ORDER BY ...)

[#1]

Hi All,

on the below Input recordset I'd like to add EndDate that would be equivalent of following StartDate - 1 partitioned by Id just like in SQL - LEAD(StartDate) OVER (PARTITION BY Id ORDER BY StartDate). Any idea how to do it?

Input:

Id	StartDate
1	2015-01-07
1	2015-06-04
2	2014-09-13
2	2015-02-03

Output:

Id	StartDate	EndDate
1	2015-01-07	2015-06-03
1	2015-06-04
2	2014-09-13	2015-02-02
2	2015-02-03

Regards,

Przemek

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD Id,

    StartDate

FROM

[https://community.qlik.com/thread/208361]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD Id,

  StartDate,

  If(Id = Peek('Id'), Date(Peek('StartDate') - 1)) as EndDate

Resident Table

Order By Id, StartDate desc;

DROP Table Table;

[#3]

Perfect, thank you, Sunny!

[sunny_talwar]

Not a problem

I am glad I was able to help