I am trying to figure out how to make a running total in KNIME that uses the below ‘Group’ and ‘Group Amount’ fields, though the running total needs to start again/reset if there are rows in between that are not part of the same group. The last column in the below table is what the results should be. I was able to make a standard running total using ‘Group loop’ & ‘Moving Aggregation’ nodes, though couldn’t figure out how to further change setting to get the amounts in the last column. Any ideas for this type of running total in the last column?
Hi @tommy - Thanks for checking into this, much appreciated. I haven’t tried a lag column with a rule engine yet - good idea - i’ll give it a go.
I added some colors to make the data cleaner looking. I’ve highlighted the values that make up trn 14 in green and the values that make up trn 23 in blue. For trn 14, a new group starts (more specifically, K26 starts on trn 13), then the running total of the debit and credit offset to zero. For trn 23, this is the “special running total” part, where even though trn 14 and trn 23 are the same group (K26), i want the row in the middle that is not part of there group (K26) to basically reset the running total - so for trn 23, the running total would have been reset and it starts again on trn 22, adding debit 17.96 plus credit 8.98 to equal 8.98.
Indeed there is a Lag Column node, a Rule Engine and a Group Loop Start. The “trick” is to identify the break-points in your table and create a group of them.
Hope this helps
Hi @HansS
Wow… I am absolutely amazed - that is genius! I also never even would have thought about the Missing Value node, that’s really smart. Thank You so much - this is exactly it, you solved it!
