topic Re: Previous 1, Previous 2, and Previous 3 in App Development

Previous 1, Previous 2, and Previous 3

ali_hijazi — Fri, 11 May 2018 14:24:44 GMT

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

Re: Previous 1, Previous 2, and Previous 3

sunny_talwar — Fri, 11 May 2018 14:26:47 GMT

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)

Re: Previous 1, Previous 2, and Previous 3

ali_hijazi — Fri, 11 May 2018 14:28:56 GMT

but I want to reset after each empCode

how to accomplish this?

Re: Previous 1, Previous 2, and Previous 3

sunny_talwar — Fri, 11 May 2018 14:30:23 GMT

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

];

Re: Previous 1, Previous 2, and Previous 3

ali_hijazi — Fri, 11 May 2018 14:31:21 GMT

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

Re: Previous 1, Previous 2, and Previous 3

ali_hijazi — Fri, 11 May 2018 14:41:09 GMT

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