topic Peek next value in the row in QlikView

Peek next value in the row

Tue, 13 May 2014 13:22:35 GMT

Hello friends,

i have data like

Input table

I-Code,S-Value,B-Value

1,45,14

2,41,13

3,26,26

4,27,17

5,18,25

Output i want to get

I-Code,S-Value,B-Value

1,45,13

2,41,26

3,26,17

4,27,25

5,18,0

Re: Peek next value in the row

jonathandienst — Tue, 13 May 2014 13:27:32 GMT

You can't look ahead, so reverse the order and look behind. Like this:

Output:

NoConcatenate

LOAD [I-Code],

[S-Value],

Alt(Previous([B-Value]), 0) As B-Value

Resident Input

ORDER BY [I-Code] DESC

Drop Table Input; // avoid unneeded synthetic key

HTH

Jonathan

Re: Peek next value in the row

Tue, 13 May 2014 14:02:58 GMT

Thanks but It did'nt working i want to subtract values

(S-Value - B-Value) but in load script

from the script i get

1,45,0

2,41,13

3,26,26

4,27,17

5,18,25

Please help!!!

Re: Peek next value in the row

anbu1984 — Tue, 13 May 2014 14:10:15 GMT

Jonathan's script gives your expected result in your first post. Now do you want to do subtract values? How?

Re: Peek next value in the row

Tue, 13 May 2014 14:21:31 GMT

Thanks jonathan it worked can you explain the script and why you use Alt and the DESC commands.

Re: Peek next value in the row

its_anandrjs — Tue, 13 May 2014 14:23:22 GMT

NoConcatenate

LOAD

I-Code,

S-Value,

if(RowNo() > 1,Previous( B-Value)) as B-Value

Resident Input

ORDER BY I-Code DESC;

Drop Table Input; //If not required do not drop table or rename the fields.

Re: Peek next value in the row

anbu1984 — Tue, 13 May 2014 14:28:25 GMT

After Desc

5,18,25 // Previous of [B-Value] is Null. Alt is used here to check [B-Value] is valid number else return 0

4,27,17 // Previous of [B-Value] is 25

3,26,26 // Previous of [B-Value] is 17

2,41,13 // Previous of [B-Value] is 26

1,45,14 // Previous of [B-Value] is 13

Re: Peek next value in the row

Wed, 14 May 2014 13:22:30 GMT

Thanks All you.

Re: Peek next value in the row

jonathandienst — Wed, 14 May 2014 14:07:32 GMT

The manual should fully explain the Alt function. It returns the first valid number from its parameter list. Here it returns 0 in the place of the null in the first Previous.

DESC means sort in descending order.

Jonathan

Re: Peek next value in the row

Wed, 11 May 2016 16:24:42 GMT

Hello Jonathan,

I am currently using peek function to look at the previous row but at the same time I would also like to see the nex row value. Is there any opposite to peek function available to achieve this?

Thank you,

Parth