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.
Thanks in advance to all for your feedback and help.
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?
About the “WARN” messages:
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…
I didn’t saw your answer.
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)
