Hello, I would like to create a transaction number for balanced accounting entries. For this purpose, I have a cumulative balance that, when it reaches 0, the next row should change its number.
1 Like
Combine debit and credit columns into a single column with positive and negative numbers
then use the moving aggregation node to calculate a running “sum”
then use an if statement to place a 1 in a new column if the moving aggregation column is 0 else place a 0
Then do a moving aggregation on that column
Then lag column to drop by 1 row
2 Likes
I had assumed that you were working with a full detail transaction export (as that is what I require). I missed the “cumulative balance” part. Depending on the report structure, you may be able to skip the first 2 steps.
thank you for answer.
I’m not sure I understand the last step. How do you lag column to drop by 1 row? And then, how do you create a number that changes with each sequence of 1?
Yes, I had already executed those steps. I had also considered step 3.
Hi @aurelienchauvin. Welcome to the KNIME community! To lag the column, there is a Lag Column node where you specify the column whose previous row value you want to include in each row.
I’ve uploaded a workflow to the hub which gives an example of the idea that can be applied using Moving Aggregation and Lag Column. I have based it on your example, and it is pretty much as @iCFO describes, but I’ve tried to make it reasonably generic in describing what it is doing so it can be easily applied to other examples in future. I may also update it in future if there are some shortcuts, or other improvements that can be applied (or any mistakes! 
)
3 Likes
Thank you very much, that’s exactly the result I wanted to achieve.
2 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.