How can I create a new column based on Column A. The new column is the sum of Column A in cell B1. Cell B2 is B1-A1. Cell B3 is B2-A2, and so forth.

KNIME Analytics Platform
1

I have column A with random numbers like

Column A
5
7
3
7
4

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.

2

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.

1 Like
3

Can you give a workflow with an example please?

1 Like
4

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.

5

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?

6

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.

7

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:

image
image1240×959 174 KB

Just being aware that the window length == number of rows + the yellow marked settings.

BR

1 Like
8

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.

2 Likes
9

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.

1 Like
10

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