LAE

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

Originally posted by: J.Lim

Hi there,
Was wondering if anyone can give me a hand on this:

CustomerNbr Date Hour Value
1 15/3 15 100
1 15/3 16 80
1 15/3 17 90
2 15/3 10 20
2 15/3 18 10

What I'm hoping to get is the difference of value in current row's value compared to prev row's value, and the difference should be 0 when it is first in group sorted by customerNbr, Date, Hour, ie end results should look like:

CustomerNbr Date Hour Value Difference
1 15/3 15 100 0
1 15/3 16 80 -20
1 15/3 17 90 10
2 15/3 10 20 0
2 15/3 18 10 -10

I have something like this, but was stuck where it always retrieve prev row's value as the first value in the group.

if firstInGroup
then
{
ValueDifference = 0
tempValue = 'Value'
}
else
prevValue = tempValue
ValueDifference = 'Value' - prevValue
emit *, tempValue, prevValue, ValueDifference

Anyone who can assist with this question would be much appreciated!


Thanks, Jess

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

Originally posted by: jodycrutchfield

Jess,

If you are using a filter node you can use this code.

# Watch for group changes
if firstExec then {
prevGroup = 0
currGroup = group
} else {
prevGroup = currGroup
currGroup = group
}

# Calculate value differences
if prevGroup != currGroup then {
diffVal = 0
currVal = col_to_check
} else {
prevVal = currVal
currVal = col_to_check
diffVal = currVal - prevVal
}

emit *, diffVal

It checks for differences in the group then calculates the diff based on the values.

Jody

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

Originally posted by: henk01

Hi Jody,

A good solution, and a good way to assign the previous currVal as prevVal, before obtaining the next currVal value. I�ve nothing much to add, apart from sharing another trick that could be useful sometimes (a trick Larry demonstrated during a training session).

It follows a similar principle, but here prevVal is assigned after the emit (or after the �diff� logic), so that the value can be used instantly during the next execution (next row, if that makes sense)

I�ve also used an agg node, so that the firstInGroup variable can be used to set the prevValue to Value (CustomerNbr as GroupBy key):


# if first in group then simply use the current value as previous Value
if firstInGroup then prevValue = Value

Diff = Value - prevValue

emit *,
prevValue,
Diff

prevValue = Value



Best Regards,

Henk

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

Originally posted by: J.Lim

Thanks both Henk and Jody. This is really helpful