topic Re: use previous to return value X rows up in QlikView

use previous to return value X rows up

samuel_brierley — Thu, 05 Mar 2015 10:15:42 GMT

Hi all,

thanks for any response, i think the title sums this up perfectly.

in the load script i want to create a field that is

ULO_Count - Previous(ULO_Count)) as [Non-Cumulative],

where previous(ULO_Count) isnt one row up but 20 for example.

thanks

Re: use previous to return value X rows up

Gysbert_Wassenaar — Thu, 05 Mar 2015 10:22:34 GMT

Use the peek function: peek(ULO_Count,-20)

Re: use previous to return value X rows up

samuel_brierley — Thu, 05 Mar 2015 10:24:00 GMT

will not that always return the 20th row from the bottom of the table?

Re: use previous to return value X rows up

Gysbert_Wassenaar — Thu, 05 Mar 2015 10:30:26 GMT

That depends on where it is used. If you use it in a load statement it will get the value from the 20th previous row. If you use it to create a variable then the table was already created and you get the value from the 20th row from the bottom. Try this script and compare the values of B with the variable vMyValue.

T1:

LOAD recno() as A, peek(A, -20) as B

autogenerate(100);

LET vMyValue = peek('A', -20, 'T1');

Re: use previous to return value X rows up

sunny_talwar — Thu, 05 Mar 2015 10:37:16 GMT

Not sure how your data is, but may be something like this:

Table:

LOAD * INLINE [

Cum

];

Table2:

LOAD Cum,

Alt(Cum - Peek('Cum'), Cum) as NonCum

Resident Table;

DROP Table Table;

Best,