Return previous row value

[#1]

hi.

i am formulating a script for qlikview whereby i hope to apply the following logic.

as you can see from the script below there is no Valid to date.

dates:
LOAD * INLINE [
Kalk_Version, Valid From, Valid To
122161, 2014-06-09,
123459, 2014-12-08,
124175, 2015-03-30,
];

what is wish to do is Populate a Valid To Date whereby  take the next ValidFrom date and minus -1. If there is no date to return then NULL.

for example

dates:
LOAD * INLINE [
Kalk_Version, Valid From, Valid To
122161, 2014-06-09, 2014-12-07
123459, 2014-12-08,2015-03-29
124175, 2015-03-30,NULL
];

any help would be greatly appreciated.

Best

Brad

[marcus_sommer]

You need to load your data with a backwards-sorting and could then per peek() access the value from the previous row.

- Marcus

[sunny_talwar]

Is this what you are trying to achieve? PFA

Best,

S

[Gysbert_Wassenaar · Accepted solution]

Temp:
LOAD Kalk_Version, date#([Valid From],'YYYY-MM-DD') as [Valid From] INLINE [
Kalk_Version, Valid From, Valid To
122161, 2014-06-09,
123459, 2014-12-08,
124175, 2015-03-30,
];

Result:

dates:
LOAD  Kalk_Version, [Valid From], previous([Valid From])-1 as [Valid To]

Resident Temp

Order by Order by [Valid From] desc;