# Dividing columns depending on their name

Hello all,

First time for me in knime community. I’ve already enjoyed other post with tips but this time I cannot find anything which match with my problem…

I’ve a file load in knime in which I have columns named with date containing some sales value eg :

Sales table :

``````Code number	01.2018	02.2018	03.2018	04.2018	05.2018
azerty1              12	     67	    279      27     181
azerty2               4      79	     21	    277	    127
azerty3              61	    195	    134	    244	     63
``````

And another table with some stock value for the same code number eg :

Stock table

``````Code number	01.2018	02.2018	03.2018	04.2018	05.2018
azerty1              71	    181	      7	    194	    139
azerty2              72	     96	     54	    300	    245
azerty3             256	     32	      8	    176	    274
``````

My goal is to calculate the stock coverage that represent the number of units in stock for each code number by applying the following rule :
30 x Stock of the month / average of the 3 previous sales month

So the result in this case should be :

Coverage table (=result of the caculation I wish to perform)

``````Code number	01.2018	02.2018	03.2018	04.2018	05.2018
azerty1				            46,8   25,7
azerty2				            71,6   51,9
azerty3				            27,6   55,9
``````

Assuming that each month I’ll load some new table one for sales and one for stock.
I tried different things and it worked when I did the math within the math node to calculate the coverage table but since I’m going to load some new tables each month I whish to make it more automatically…
Any ideas of how to perform this calculation more automatically?

I hope my example is clear enough otherwise don’t hesitate to ask for clarification.

Best regards,
Nicolas

Hi,
Let me make sure I have got it perfectly:
You want to calculate for example:
(30 * 194) / ((12+67+279)/3)
Right?

Hi armingrudd,

Thanks for you’re reply. Actually it’s :
(30 * 194) / ((67+279+27)/3)
Hope it clarify.
Do you have some good idea to perform this calculation?

OK,
That’s so easy in KNIME.
Would you mind if I share the workflow in a few hours?
I will do this as soon as I get back home.
Best,
Armin

2 Likes

This would be amazing armingrudd!!!
Thank a lot for your help.

Here we go!

The top flow reads the “Stock” table then loops over columns (enforces exclude on “Code number”). The String Manipulation node finds the month number and the Column Expressions node produces three columns: the “current” column which contains the same value as the current column in the loop (this column is created to be joined to the other flow without any conflict in column names). The “minus1” and “minus2” columns which contain the column name for 2 previous columns. Then Table Row to Variable node to create flow variables and the Rule Engine Variable to create the variable for IF Switch node.

The bottom flow reads the “Sales” data set and a column filter to include the columns for the current month and 2 previous months (by using the flow variables). The joiner to join the current month stock to the flow. The IF Switch creates empty table if the month is less or equal to 2 (as there won’t be two previous columns and considering your desired output) but for the other cases, the Math Formula gets the formula from String Manipulation (Variable) node and apply it to the data. The Column Filter node keeps the “Code number” and the coverage value and the Column Rename changes the column name from coverage to current column name from the loop start (which is the month and the year). End IF, Loop End and finally another Column Filter to remove additional “Code number” columns.

Please Check the workflow and let me know if this is what you want:
coverage.knwf (87.7 KB)

Best,
Armin

4 Likes

Woaw amazing thanks a lot for this armingrudd!
I’m checking this workflow deeply in the evening and I’ll tell you… but anuway it seems to work perfectly!

1 Like

Hi armingrudd,

Actually I still have a problem see below console message :

WARN Math Formula 2:21 No such column: missing
WARN Rule Engine Variable 2:19 Line: 5: Not a valid flow variable: month ({knime.workspace=knime.workspace" (STRING: C:\Users\I0265532\Documents\KNIME)})
\$\${Smonth}\$\$ = “01” OR \$\${Smonth}\$\$ = “02” => “bottom”
^
WARN Column Filter 2:5 Errors overwriting node settings with flow variables: Unknown variable “currentColumnName”
WARN String Manipulation (Variable) 2:25 No such variable “currentColumnName” of type STRING
WARN Math Formula 2:21 No such column: missing
WARN Math Formula 2:21 No such column: missing
WARN Table Row to Variable 2:32 Table has 1204 rows, ignored all rows except the first one
WARN End IF 2:18 The structures of both active input data tables are not compatible.
WARN Math Formula 2:21 No such column: missing
WARN Table Row to Variable 2:32 Table has 1204 rows, ignored all rows except the first one
WARN End IF 2:18 The structures of both active input data tables are not compatible.
WARN Math Formula 2:21 No such column: missing
WARN Table Row to Variable 2:32 Table has 1204 rows, ignored all rows except the first one
WARN End IF 2:18 The structures of both active input data tables are not compatible.
WARN Math Formula 2:21 No such column: missing
WARN Table Row to Variable 2:32 Table has 1204 rows, ignored all rows except the first one
WARN End IF 2:18 The structures of both active input data tables are not compatible.
WARN Math Formula 2:21 No such column: missing
WARN Table Row to Variable 2:32 Table has 1204 rows, ignored all rows except the first one
WARN End IF 2:18 The structures of both active input data tables are not compatible.
WARN Math Formula 2:21 No such column: missing
WARN Table Row to Variable 2:32 Table has 1204 rows, ignored all rows except the first one
WARN Math Formula 2:21 No such column: 08.2017
WARN Math Formula 2:21 No such column: 08.2017
WARN Math Formula 2:21 No such column: 08.2017

Any idea?

If you keep the input data structure the same as one you provided (except it is possible to add new months - I tested the workflow on a 12 month period data set without any problem), these messages are OK and there is no problem.
But I see some WARN messages because of missing some columns like “08.2018”. I guess your input has for example month 09 but no 08 and therefore the previous column which would be 08.2018 cannot be found.

Anyway, if you want the workflow to apply perfectly to your data, I need your true data structure.

Best,
Armin

Sorry for changing my message…
Well it’s working now for the months 3 to 5 but after I have either ? or 0
For some of my code it’s totally normal but it’s the same on the entire table for these months.

Would you provide a screenshot like this which shows your input data?

Sure here it is :
Workflow :

Stock :

Sales :

Result :

mmmmh it works for you and not for me awkard…

Is it possible for you to provide just a few first rows of those Column Resorter nodes output?

you have the printscreen 4 message above

No, I want you to use a row filter node to include first few rows and then an Excel Writer or a CSV Writer to create the files. So that I can use your input without any possible differences.

Ok Let me a few min I’m on it

1 Like

Here it is

STOCK.xlsx (3.5 KB)
VENTES.xlsx (3.5 KB)

I will be back soon…