LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mgp

I would like to create two columns. One would refer to a value in the prior row, and the other would refer to a value in the following row. For example:

Customer 123
Customer 456
Customer 789

On row 2 (customer 456) I would like to emit the row prior (Customer 123) into column 1
On row 2 (customer 456) I would like to emit the row following (Customer 789) into column 2

How can i accomplish this?

I tried creating a temporary variable within the Agg node but have not been able to get it to work.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

You can't read forward. You can retain a value from a prior record, but there's not an option to read ahead.

Two solutions:
Solution 1) read record 1, hold it's value
read record 2, hold that value
read record 3, outputting all three values

To hold a value from prior record, in a filter do this:

if firstInGroup then {pv1 = null pv2 = null}
emit pv1,pv2,value
pv1 = pv2 #must be after the emit
pv2 = value #must be after the emit


Solution 2) assign a unique ROW number to each record with a filter, then use a JOIN or LOOKUP node twice
The first time, use
LeftKey= rownum +1
RightKey = rownum
the second time, use
LeftKey = rownum
RightKey = rownum +1