FIFO stock calculation

[4nak1n]

Hi guys, i have a dataset as below, with the desired output (named: “init rate”) at the last column:

ID	Date_order	Production_weight	Production_rate	Stock_Available	Desired_output
A	0	100000	4.2	0	4.20
A	1	30000	4.5	100000	4.27
A	2	26000	4.4	130000	4.29
A	3	30000	4.5	132000	4.33
A	4	26000	4.4	138000	4.34

“Init rate” will be the name of the desired output column
The logic is, which will apply for each ID (many IDs will be in the dataset, I use only one for example):

• If the date order is 0, then the “init rate” is equal to the “production rate”
• Else the formula will be calculated as follows:

(“Stock Available” * “Init Rate for previous date” + “Production weight” * “Production Rate”) / (“Production weight” + “Stock Available”)

for example, row 1: (100000 * 4.2 + 30000 * 4.5) / (100000 + 30000) = 4.27

The tricky part is you have to use the newly calculated “init rate” of the previous row for the current row calculation.

I can archive it easily in Alteryx, but I am not sure how to do it in Knime.

Thank you for your help.

[mlauber71]

@4nak1n the new KNIME expression node can do that.

https://docs.knime.com/latest/knime_expressions_guide/index.html

$["column_name", -1] Value of the column “column_name” from the previous row

Also there is the lag column node and the Column Expressions node can also do this, you have to activate the function though:

Converting null to zero in "Expression" and "Column Expression" node.

You will have to activate multi-row access [image]

In your case you might have to use a loop or refer to the result if the calculation. Here is a discussion that might give some insights

Convert data input level (level 1, 2, 3) into tree (1, 1.1, 1.1.1, 1.1.2...)

Hi @christoph_knime , welcome to the KNIME community If you are happy to write some code, the Column Expressions node (in the KNIME Expressions extension) can derive this by cumulatively calculating the “tree”. var treeOutput // variable for cumulative tree calculation // define the column representing the current Level currentLevel=column("Level (Input)") /* define some support functions */ function getTreeToLevel(tree,level) { if (tree==null || tree=="") { return "0" …

[mlauber71]

@4nak1n well there is a complication to this and I solved it by using a somewhat over engineered workflow but it does work. Maybe there is a better way to do it. I iterate over the table and always work with a current row and the last one and then send the result back and do the next calculation.

FIFO stock calculation - KNIME Forum (82403) – mlauber71

FIFO stock calculation - KNIME Forum (82403).knwf (105.7 KB)

---

And for the fun of it there is the Python version of this …

[4nak1n]

I ended up also using Python to solve it. But I am amazed at your solution. Thank you so much for your help. It worked perfectly.