Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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:

IdStartDate
12015-01-07
12015-06-04
22014-09-13
22015-02-03

Output:

IdStartDateEndDate
12015-01-072015-06-03
12015-06-04
22014-09-132015-02-02
22015-02-03

Regards,

Przemek

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG

View solution in original post

3 Replies
sunny_talwar

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;


Capture.PNG

View solution in original post

Not applicable
Author

Perfect, thank you, Sunny!

sunny_talwar

Not a problem

I am glad I was able to help