I am having a hard time trying to figure out the following:
So basically what i want it to do is calculate the volume_mt per row by doing “previous volume_mt - con_prd_vol_mt”.
Using the LAG node obviously only gives me -100, as the rest has not been calculated yet.
I’ve tried to use a recursive loop, as I need the newly calculated value as input for my next row. However I still did not manage to get it working. Anyone that could help me out?
Welcome to the KNIME community!
You can solve this in a several ways but since loops usually are quite resource intensive (depending on the size and complexity of the dataset) I’d like to stay away from them as much as possible.
A way to solve this is via the Moving Aggregation node:
Here, I first apply the start volume to all rows via the Missing Value node.
Next, with the Moving Aggregation node, you can create a moving sum of
con_prd_vol_mt. This to be used later on to substract it from the earlier defined starting volume.
With the moving sum determined, a simple math calculation in the associated node to update the volume_mt should do the trick.
Note: use the Convert to Int option if you want to keep the values as integer.
Moving aggregation - volume calculation.knwf (26.8 KB)
Hope this helps!
Thanks Arjen, that worked like a charm!
Still had to use a Group loop in my case as all the steps have to be applied for each customer (defined by 4 columns) separately. But that was a lot more straight forward than the recursive loop.