Looping over columns and subtracting the next column values from the current column values

[t_brouwer]

Dear KNIME community,

I am new to KNIME, so sorry for my ignorance. I have an excel sheet containing current stock value (Total Stock) + the weekly demand (week1, week2, week3, up to week99).

I am trying to create a new table that has all the results of current stock - next week = result of current week. So the result of week 1 would be Total stock - week1 = ResultWeek1, then ResultWeek1 - week2 = ResultWeek2, then ResultWeek2 - week3 = ResultWeek3 and so on.

My source data looks like this:

In excel the formulas would look like this:

And the output would be this:

I have tried the math formula (multi column) but I cannot figure out how to do it.

Does anyone have an idea how I can do this using only the standard nodes? (Excel file attached)

Thanks a lot for your support!

Currect Stock + Weekly Demand.xlsx (27.0 KB)

[AlexanderFillbrunn]

Hi and welcome to the KNIME Forum! For a beginner, this is actually not such an easy task. I have attached a solution below (and I am sure there are 100 other ways to do it).

Let me briefly explain what I am doing in the workflow:

• We read the Excel file with an Excel Reader
• With the Column Name Extractor we get a table with the column names from the input table, each column name in one row
• From this list, we remove Total Stock
• Now we do a cumulative aggregation that builds lists of columns we want to sum up, i.e. [week1], [week1, week2], [week1, week2, week3], etc.
• Now we go through these lists individually in a loop. The Table Row to Variable Loop Start node turns each of the column lists into a flow variable. The result column (week1, week2, week3, …) is also turned into a flow variable.
• We use a Column Aggregator node to sum up all the columns in the current list (iteration 1: [week1], iteration 2: [week1, week2], …). Here the column selection and the output column name are controlled via the flow variables created by the Table Row to Variable Loop Start. This is done in the Flow Variables tab of the config dialog.
  
  
• Then we use a Column Expressions node to calculate the difference between Total Stock and the sum that was just generated by the Column Aggregator node.
• We now only keep the result of that, i.e. the column with name week1 in iteration 1, week2 in iteration 2, etc. Again we use a flow variable to configure this node dynamically with the right column name.
• We collect the results from each iteration and append them as columns to a table.
• In the end we need to attach the Total Stock column back onto that table with a Joiner node.

Here is the workflow file:
Column Aggregation.knwf (125.0 KB)

Let me know if you have any questions.
Kind regards,
Alexander

[rfeigel]

Here’s an alternative without loops.

Current Stock_Weekly Demand – rfeigel

[lelloba]

Hi @t_brouwer,

here is my solution (now there are only 98 left )

Looping over columns and subtracting the next column values from the current...

Works without variables and with any number of rows and columns without manual adjustments, but one loop is required.

Have a nice day,
Raffaello Barri

[AlexanderFillbrunn]

Hi,
This is super elegant. Thank you for sharing your solution! I think we can reduce the number of nodes even more! In your Math Formula (Multi Column) node, you can choose Wildcard selection with week* as the pattern. Then Total Stock will not be multiplied by -1 and you can skip the additional Math Formula.
Kind regards,
Alexander

[rfeigel]

Good idea! I uploaded the revised version.