# FIFO stock calculation

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.

@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:

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

3 Likes

@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.

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

4 Likes

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.

1 Like

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