Using previous calculated rows for new rows

[danubio1971]

Hello, I have the following issue.

In the table, I have some part #s, the Order column, and the amount in store.
If or while the part number is the same as the previous cell, then I need to make Order - Store values.
I tried with the lag node, but it is not working, as I need the previous row with the calculation.
I tried with the Java Snippet with the ROWINDEX, but I was not able to have ROWINDEX - 1…I don’t know how to do that.
The moving average does also not work here and the recursive loop, I have no idea how to use that.
Any ideas? Thank you very much!

[aworker]

Hi @danubio1971

Could you please upload just this few rows table in a workflow? I’ll figure out and upload back a solution from there.

Best regards

Ael

[danubio1971]

Sample_Parts.knwf (8.0 KB)
Hello @aworker, here is the workflow, thank you!

[aworker]

Hello @danubio1971

The workflow is reseted and hence I cannot read the data. Would you mind please to post it again ? When exporting it, please make sure the “Reset workflow before export” box is not selected.

Best

Ael

[danubio1971]

Sample_Parts.knwf (12.8 KB)
Sorry for that @aworker, here is the updated one.

[morpheus]

Hi @danubio1971,
i don not unterstand your logic. What is the difference between Part# 149003 and 19041171A which triggers a different calculation?

BR

[aworker]

Hi @danubio1971

Thanks for the data. Please find the solution here below :

20200422_PIKAIROS_Using previous calculated rows for new rows.knwf (29.8 KB)

What you describe as rule in your request and what is written as rules in the last column of your table are not the same. I hence followed your “If while” explanation. Whatever is the right rule, the spirit of the solution is here. You need to lag and compute new columns before you can apply the rule-engine node on your columns because of the lag needed on your data. Hope this is of help.

Best regards

Ael

[danubio1971]

Hello @aworker, thank you very much for the response.
The only problem with this is that you started working with the file that already have the solution. Meaning that the column Order should be 75 always for the first part # and 220 for the second one.

[danubio1971]

Hi @morpheus, Part # 149003 is being ordered by a client, and in the store we have 4 locations with supplies for that part, that is why we have 4 rows in this case. So the order is for 75 and in store, in the first location we have 1631, so we should have 1631-75 and give that updated number to the system, all other locations can be dismissed.
When the part# changes in the row, then we should start the same process as described before.
And in the case we have positive numbers, for example 75-20, the we should keep going through all the locations until we finish our order.
Hope that clarifies the issue.

[atothep]

Just in case you still haven’t found a good solution to your problem…
I believe the best way to solve it is the Java Snippet node. There you should create some “global” variables that hold the ordered amount and how much of it has already been satisfied by your “store”.
The core part of the Snippet then iterates over all records row by row. In that part you can decide what calculations to do.
Here is an example of a Java Snippet that I use to do a filter-like operation that keeps only records if their value has changed by at least 50% compared to the last value that was kept. From your description I think this should come very close to what you are looking for:

Good luck!

[Daniel_Weikert]

maybe connect aworker’s solution with a group loop to get your desired result

[morpheus]

Hi @danubio1971,
for your first Part# in your example all formulas refer to cell b2. For the second one all formulas refer to the previous row. Which formula is the correct one?

BR

[atothep]

If I understand you right, the attached WF (only one simple Java Snippet) should do the job

parts_order_problem_knime_forum.knwf (7.5 KB)

Anyway, I have the feeling that the way your data is structured is not optimal. I think it would be more natural in this case to have two tables:
One table - much like the one you showed - representing your current storage (basically two columns: part# + amount)
And another table representing the orders (which in this case would contain only two records, because there are only two orders)
Joining these two tables into one does not necessarily seem a good idea to me…