Previous 1, Previous 2, and Previous 3

[ali_hijazi]

Hello got the following data:

EmpCode, Value

101, 5

101,8

101,76

102, 6

103, 66

103,90

103,150

103,75

....

What I want to accomplish is the following:

EmpCode, Value, Previous_1_Value, Previous_2_Value, Previous_3_Value

101, 5, NULL,NULL,NULL

101,8,5,NULL,NULL

101,76,8,5,NULL

102,6,NULL,NULL,NULL

103, 66,NULL,NULL,NULL

103,90,66,NULL,NULL

103,150,90,66,NULL

103,75,150,90,66

....

i know the previous 1 I can use previous function if I order data by empCode

but how to accomplish previous_2, and Prvious_3

Kindly Advise

[sunny_talwar]

You can use Previous(Previous(...)) for going back 2 or you can use Peek(..., -2). Similarly, for 3 you can do Previous(Previous(Previous(...))) or Peek(..., -3)

[ali_hijazi]

but I want to reset after each empCode

how to accomplish this?

[sunny_talwar · Accepted solution]

Do the if statement check the same way

Here is a sample

Table:

LOAD *,

If(Peek('EmpCode', -1) = EmpCode, Peek('Value', -1), 'NULL') as Previous_1_Value,

If(Peek('EmpCode', -2) = EmpCode, Peek('Value', -2), 'NULL') as Previous_2_Value,

If(Peek('EmpCode', -3) = EmpCode, Peek('Value', -3), 'NULL') as Previous_3_Value;

LOAD * INLINE [

    EmpCode, Value

    101, 5

    101, 8

    101, 76

    102, 6

    103, 66

    103, 90

    103, 150

    103, 75

];

[ali_hijazi]

when I reach empCode 103 then the previous should return null, and so does previous_2, and previous_3 for the first row of empCode 103

then in the second row I get a previous value but null for previous_2, and Previous_3

then in the third row I get a previous value , and a previous_2 value, but null for previous_3

and so on

[ali_hijazi]

oh yes
I totally forgot about the second parameter of the peek function