I have column A with random numbers like
I want Column B to start with the sum of all values in Column A, so 26 in this case. Then I want the second cell of Column B (B2) to say 21 (26-5). Third cell of Column B (b3) to say 14 (21-7). And so forth.
There are probably a bunch of ways to do this, but you could use a “counter generation” node to add a column with the numerical order and then sort it in reverse order, then do a running total, then sort it back.
Can you give a workflow with an example please?
ReverseTotalRun.knwf (24.0 KB)
Sorry. “Moving Aggregation” would have been more helpful than running total since that is the actual name of the node.
This is very helpful, thanks. Last question, how do I get the sum of the Column A to be a new entry at the bottom of Column A first, and then do everything you did in terms of moving it to the top row and then subtracting as you did?
You can use the group by node to sum the column, then you can add it to the bottom with the concatenate node.
I will add it to the workflow for you when I get back into the office.
Hello @kyleplaytime and welcome to the KNIME forum.
Just for the records; your initial request can be solved with just one node, this is ‘Moving Aggregation’ with the following configuration:
Just being aware that the window length == number of rows + the yellow marked settings.
I would caution against manually entering the row numbers in order to get a 1 node solution unless this is for some single use workflow that will be deleted after use.
As a general rule I believe that everything in KNIME should be focused on a dynamic approach to avoid future problems. That being said, I think @gonhaddock’s approach is certainly cleaner if you control the row numbers via a row count and flow variable so that it dynamically adapts to updated data with variable number of rows. I can throw it into the workflow as well if you are not familiar with flow variable use yet.
ReverseTotalRun.knwf (31.3 KB)
Here is the workflow with the total added to the bottom of column A 1st, and then both approaches to the running calculation.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.