# row calculation based on multiple columns (with changing names)

Dear community,
first of all, many thanks for that great platform & software, it already saved me a lot of time in my daily work!
I personally don´t have a programming backround, but already have some workflows running.
And now i am at a point in my new workflow, where i do not know how to proceed.
I invested already some time to search for a solution, but unfortunately could not find a proper one ;-(
So therefore any help from you is highly appreciated.

This is my use-case i have (hopefully it is visible properly):

Knime_use_case.xlsx (197.9 KB)

I want to calculate line 6+11 (stock evolvement) in this example, where i have the following problems;
a) due to a weekly update, the column header (CW = Calendar Weeks) is going to be changed week by week. Starting with always the current calendar week.
b) the stock-evolvement-calculation of the current week is always based on a fixed column (stock)
c) whereas the other weeks have to be calculated based on the previous weeks stock

This is my current data-set example:

Knime_data_set.xlsx (9.7 KB)

Hopefully i have explained it clearly…

best regards, MS

Hello @Mathias_S ,

I have few questions for you.

1. Is your data like in the first screenshot or like in the second one?
2. Computation in screenshot 1 is clear (83217+13212-20000=90005); how does it work in the second one? Eg: in the second photo, current stock - requirements + supply in row 2 leads to -8000 and it sounds strange to me

Let me know

Have a nice day,
Raffaello

Hello @lelloba ,

1. i´ve prepared the data´s already according to the last screenshot (second one), but for sure can be changed if necessary

2. the calculation is always for the current week:
current stock part number A - Requirements part number A + Supply part number A .
Example for part A: 83.217-13.212+20.000 = 90.005
Example for part B: 43.152-5.202+10.000 = 47.950

For the following weeks it is then always:
stock from previous week part number A - Requirements part number A + Supply part number A.
Example for part A: 90.005-15.168+20.000 = 94.837
Example for part B: 47.950-6.300+7.000 = 48.650

Hint; in my original data´s i have many more different part numbers (currently approx. 400, but will be further extended, in case the workflow is working).

FYI, I added all data´s incl. the excel-formula´s also as excel in my initial message. Don´t know, if you can open it.

I really hope, you or somebody else can help me with that issue.

thank you & have a nice day,
Mathias

Hello @Mathias_S ,

sorry if it took so long and thank you for your patience.

Here is a small workflow I have prepared:

Keep in mind that in the input dataset the attribute “Start value stock” is always zero, except for the first line on each part number. See image below.

The computation has been made in R, because I could not find a way to do it with simple nodes. Maybe you want to replace the R script node with a Python script node, so you don’t want to install R on your PC.

Have a nice day,
Raffaello

1 Like

have you tried col_sum with math multi column?
br

2 Likes

Hello Mathias,

I have updated my solution. You can find both alternatives at the same link above.
Thank you @Daniel_Weikert for the suggestion!

Have a nice day,
Raffaello

2 Likes

Hello @lelloba & @Daniel_Weikert ,
many many thanks, it worked !!
Took the solution with col_sum (Math-Formula-Node).

2 Likes

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