use previous to return value X rows up

[samuel_brierley]

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

[Gysbert_Wassenaar]

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

[samuel_brierley]

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

[Gysbert_Wassenaar]

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');

[sunny_talwar · Accepted solution]

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

Table:

LOAD * INLINE [

    Cum

    5

    5

    5

    5

    30

    30

    32

    32

    32

    32

    32

    32

    32

    40

    40

    41

    43

    43

    43

    50

];

Table2:

LOAD Cum,

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

Resident Table;

DROP Table Table;

Best,

S