Hello friends,
Objective: I would like some help implementing a rule to identify the most recent invoices that account for the final inventory balance.
In the example below, I join the Invoice table with the Inventory table. Since the Inventory table shows 4 units of SKU ABC for company “01”, I need to retrieve only the most recent documents that cover the quantity of 4.
In the example, the correct result would be to consider only these invoices:
000005 = 2
000004 = 1
000003 = 5
In summary, if I have 4 units in inventory, invoice “000005” only covers 2, so I need to find the next most recent invoice. Invoice “000004” is also not enough because, so far, 2 + 1 = 3, and I need 4. Invoice “000003” then becomes sufficient.
Note: The invoice quantities do not need to match the 4 units exactly. The important thing is for the system to “stop” as soon as it finds the last invoice that supports the total.
Invoices “000002” and “000001” would be discarded.
How could I implement this rule? The best simple aproach using Knime nodes (No Python)
It is important to consider that I have hundreds of SKUs and companies. I would use this rule within a Group Loop.
I know there are several ways to do this. Maybe using a recursive loop would be interesting until it finds the total. However, I haven’t been able to do it in a simple way using a group loop.
I am attaching a workflow to make it easier.
Invoice Table
| Company | INVOICE NUMBER | INVOICE DATE | SKU | QTY SKU |
|---|---|---|---|---|
| 01 | 000005 | 2026-05-14 | ABC | 2 |
| 01 | 000004 | 2026-04-02 | ABC | 1 |
| 01 | 000003 | 2026-04-01 | ABC | 5 |
| 01 | 000002 | 2026-02-05 | ABC | 1 |
| 01 | 000001 | 2025-12-31 | ABC | 1 |
Inventory Table
| Company | SKU | QTY SKU |
|---|---|---|
| 01 | ABC | 4 |
Inventory_Last_suported Invoices.knwf (52.0 KB)
