Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

Previous 1, Previous 2, and Previous 3

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

I can walk on water when it freezes
1 Solution

Accepted Solutions
sunny_talwar

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

];

View solution in original post

5 Replies
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
Partner - Master II
Partner - Master II
Author

but I want to reset after each empCode

how to accomplish this?

I can walk on water when it freezes
sunny_talwar

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
Partner - Master II
Partner - Master II
Author

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

I can walk on water when it freezes
ali_hijazi
Partner - Master II
Partner - Master II
Author

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

I can walk on water when it freezes