Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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)
but I want to reset after each empCode
how to accomplish this?
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
];
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
oh yes
I totally forgot about the second parameter of the peek function