topic Re: Return previous row value in QlikView

Return previous row value

Tue, 13 Jan 2015 11:44:21 GMT

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

Re: Return previous row value

marcus_sommer — Tue, 13 Jan 2015 11:57:36 GMT

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

- Marcus

Re: Return previous row value

sunny_talwar — Tue, 13 Jan 2015 11:58:45 GMT

Is this what you are trying to achieve? PFA

Best,

Re: Return previous row value

Gysbert_Wassenaar — Tue, 13 Jan 2015 11:59:52 GMT

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;