I need to derive end date from rowno( 2) -1 of start date. Need help in Scripting.

[Anonymous]

Below is my data.

Date1

12/26/2008

12/26/2009

12/26/2010

12/26/2011

12/25/2012

12/25/2013

12/25/2014

12/25/2015

12/24/2016

12/24/2017

Output should be like .

Date1	Enddate
12/26/2008	12/25/2009
12/26/2009	12/25/2010
12/26/2010	12/25/2011
12/26/2011	12/24/2012
12/25/2012	12/24/2013
12/25/2013	12/24/2014
12/25/2014	12/24/2015
12/25/2015	12/23/2016
12/24/2016	12/23/2017
12/24/2017	Today()

Plz help.

[jonathandienst]

If I understand you correctly, you want EndDate to be the start date of the following row?

To do this, resident load the data sorted by date descending:

     LOAD Date1 as StartDate,

          Alt(Previous(Date1), 0) as EndDate,

          ...

     Resident Table1

     Order By Date1 DESC;

[Anonymous]

Thank you , it is giving me the output which is similar to my requirement. but

1. i would not require it in desc format. I need it as it is.

2. I would like to get the data using peek function...

Plz help

[ahaahaaha]

Hi,

Maybe something like this

Table1:

LOAD

Date#(Date1, 'MM/DD/YYYY') as Date1

Inline [

Date1

12/26/2008

12/26/2009

12/26/2010

12/26/2011

12/25/2012

12/25/2013

12/25/2014

12/25/2015

12/24/2016

12/24/2017];

Table2:

LOAD

Date(Date#(Date1, 'MM/DD/YYYY'), 'MM/DD/YYYY') as StartDay,

Date(Date(Date#(Previous(Date1), 'MM/DD/YYYY')) - 1, 'MM/DD/YYYY') as EndDay

Resident Table1

Order By Date1 desc;

NoConcatenate

Table3:

LOAD

StartDay ,

If(Isnull(EndDay) = 0, EndDay, Today()) as EndDay

Resident Table2;

DROP Tables Table1, Table2;

Result

Regards,

Andrey

[Anonymous]

Thank you Andrey..

Will the same be done with the help of peek function.using a for loop like.

Let vdate= i;

for i = 1 to max(rowno())

If(peeK('date1',1) -Peek('date1',2) = 365, Peek('date1',2) -1, Today()) as Enddate

Next

I need script for the above example..where i increments till max row.

[jonathandienst]

>>1. i would not require it in desc format. I need it as it is.

Qlik has no lookahead function, so you need to sort it in desc order to do the equivalent.This has no affect on the front end sorting.

2. I would like to get the data using peek function...

There is really no need. Peek is used to read previous output rows, Previous reads previous input rows. In your case, it makes little difference, although Peek may be very slightly slower.

[jonathandienst]

I would not do it in a loop like that. Avoid row by row operations except for extremely small data sets. But the script will not work - you can't embed a for loop in a LOAD statement.