Peek next value in the row

[#1]

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

[jonathandienst · Accepted solution]

Hi

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

[#3]

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!!!

[anbu1984]

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

[#5]

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

[its_anandrjs]

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.

[anbu1984]

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

[#8]

Thanks All you.

[jonathandienst]

Hi

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

[#10]

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