Hi @Rokank, although it would be nice to say otherwise, and on the face of it, the requirement is straightforward, this doesn’t feel like a totally trivial problem. KNIME is great for dealing with sets of data which match nicely, but when it comes to partial-matching of data (and having to “generate” new records as is required here where budgets and cost cross a boundary),it becomes less easy to achieve with the standard nodes. So effectively trying to match up two datasets of the form you have becomes a bit of a programming task which could probably be achieved relatively easily with a python script, but less easily with standard nodes.
So, the way I approached this was using something similar to an approach I used here for matching Shares where blocks of shares needed to be allocated to sales.
The idea is to turn your data into “sets” that KNIME nodes can handle more easily. If you imagine your budget allocations as stacks of dollar bills, with piles allocated by PO and WO, and similarly your transactions each take dollar bills one-at-a-time from each available WO stack in order, then we can work out the sequence in which costs are allocated to budgets, and then count the “stacks” of dollar bills allocated at the other end.
The attached workflow uses exactly this approach. For each dollar in both the Budgets and the Transactions, it uses One Row to Many to create a row. These rows are your stacks of dollar bills (one row equals one dollar). It then joins the costs stacks to the budget stacks by WO number combined with the sequence of dollar-bills in the stacks. At the far end it counts (rather than sums!) the dollars in the stacks, and summarises the data back into the form you require.
Of course if your costs and budgets go down to the “cents”, then you have to multiply up at the beginning (as per annotation in the workflow) by 100, which will clearly greatly increase the size of the stacks, and then at the far end of the workflow divide the results back down by 100 again. I don’t know how well that would scale if you really need cents (or if your budgets run to significantly larger values!), but at the moment I cannot think of another (non-programming) way to achieve this.
Somebody else may have a better idea!
Allocate txn to budget.knwf (46.5 KB)