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.

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.

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