Special Running Total

#1

Hi,

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?

*Do not need *Need this kind
Project Transaction Amount Flag Group Group Amount Standard running total Special Running total
K17 1 $8.98 Flag K17 $8.98 8.98 $8.98
K17 2 ($8.98) Flag K17 ($8.98) 0 $0.00
K17 3 $8.98 Flag K17 $8.98 8.98 $8.98
K17 4 $8.98
K17 8 $8.98
K17 9 $8.98
K17 10 $8.98 Flag K17 $8.98 17.96 $8.98
K17 11 ($8.98) Flag K17 ($8.98) 8.98 $0.00
K17 12 $8.98 Flag K17 $8.98 17.96 $8.98
K26 13 $8.98 Flag K26 $8.98 8.98 $8.98
K26 14 ($8.98) Flag K26 ($8.98) 0 $0.00
K26 15 $17.96 Flag K26 $17.96 17.96 $17.96
K26 16 $8.98
K26 22 $17.96 Flag K26 $17.96 35.92 $17.96
K26 23 ($8.98) Flag K26 ($8.98) 26.94 $8.98
K26 24 $8.98 Flag K26 $8.98 35.92 $17.96
0 Likes

#2

Hi, I haven’t got the point yet :wink:
However, I would try a Lag column and a Rule Engine in order to define the “jump” in column “Group”.

Can you tell me why transaction 14 should be 0 and transaction 23 should be 8.98?
I’ll try to help you with an example workflow.

Greetz, Tommy

1 Like

#3

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.
1

0 Likes

#4

Hi @John-k

Take a look at this workflow special_running_total.knwf (64.3 KB)


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

Gr. Hans

5 Likes

#5

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

2 Likes

closed #6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

0 Likes