Allocate Transactions to a budget

[Rokank]

Hi Knime experts,

I am trying to allocate a transaction table to a budget table where there are multiple budgets available for one transaction. When a transaction exceeds a budget of a PO, it proceeds to the next PO where funds are available.

For example:

Table 1 (Budget):
|PO|WO|Budget|
|PO123456|ABC111|500|
|PO246812|ABC111|200|
|PO369246|ABC222|100|
|PO483692|ABC222|100|
|PO548369|ABC333|100|

Table 2 (Transactions):
|WO|Name|Cost|
|ABC111|Matt|300|
|ABC111|Candace|150|
|ABC111|Jessica|200|
|ABC222|Roger|100|
|ABC222|Sam|50|
|ABC333|Jamie|100|

Expected Output:
|WO|Name|Cost|PO|
|ABC111|Matt|300|PO123456|
|ABC111|Candace|150|PO123456|
|ABC111|Jessica|50|PO123456|
|ABC111|Jessica|150|PO246812| <= Jessica record exceeded $500 within PO123456
|ABC222|Roger|100|PO369246|
|ABC222|Sam|50|PO483692|
|ABC333|Jamie|100|PO548369|

Thanks in advance

[Luca_Italy]

hi @Rokank,
suppose to have a 500$ limit, and i’m reaching a 450$ aggregate: if i’ve to add another 100$ i can split 50$ on the same PO and the rest in other one, or the entire 100$ have to be allocated into another PO?

Luca

[Rokank]

Split 50$ on the same PO and the rest in other one. (Refer to Jessica in ‘Expected Output’)

Thanks

[takbb]

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)

[takbb]

I wasn’t particularly happy with the solution above because whilst it works, it really would use large amount of resources if you had very large budgets to allocate, and it would be disappointing if I didn’t at least try to offer a “simpler” programming solution too.

As a result, I’ve fallen on my old friend the java snippet node, using my “favourite” technique where it can remember cumulative information as it proceeds down the rows from top to bottom.

So a combination of KNIME standard nodes to do the grunt work, and then a java snippet to provide the refinement and rules, resulted in the following workflow which I have attached along with the original workflow

Here, the joiner brings together all cost rows and joins them with all possible purchase orders for the required WO, and this is then sorted in PO and transaction sequence order, resulting in the following table of “possibilities” that need to be processed

A java snippet is then used to run down the list, keeping track of PO and WO budgets, along with the amount “remaining” on each transaction as it is processed. Where a PO runs out of money, or the cost of a transaction becomes fully allocated, rows are marked for deletion.

(Note that it doesn’t actually do anything with the tracked “WO” budgets, although it keeps a track of them, and it wouldn’t generate any errors if a transaction is not fully covered by the available POs. That eventuality could be a potential improvement, or you can use simple aggregations to find if this ever occurs, but this workflow assumes that all transactions have sufficient budgets!)

The required deletion then occurs, and following some tidy up of column names and ordering, the result is returned:

As this is not a “brute force” solution like the previous offering, this should scale happily with budgets and costs of any size, but of course it should undergo additional testing, as it contains hand-coded script and therefore has plenty of scope for the introduction of bugs.

Allocate txn to budget 2.knwf (72.9 KB)

[bruno29a]

Hi @takbb . This is one of the most interesting project. I started looking into it yesterday, but had to stop as I ran out of time (it was past 1am).

I thought of different ways of doing it, but the general idea was to keep track of 2 things, basically Budget Left, which would start with what’s in Budget and decrease as the Budget is used, and Cost Left, which would basically be the reverse of your AllocatedCost (I would use these 2 new columns so that I don’t touch the original values of Budget and Cost).

The logic would be to allocate Cost using Budget from the “first” PO (implement sequence/rank) for the WO. As Cost is allocated, Budget is removed. If all Cost is converted, move to next name, else move to next PO.

One approach I was looking into, and using Knime nodes only, would be via recursive loops. However, this was not easy to implement. While setting up the recursive loops, I ran it as a test, but did not configure it properly, and ended up crashing , and by the time I’d reboot, + did not save the workflow (I know, one of the most amateur move ), I went to sleep instead.

This most direct and most efficient approach that I ended up thinking of is doing this via Java or Python, as this project would easily be implemented using arrays and with the logic described above, however, I’m not proficient in either language (I can read them, and can write simple code) - well actually that was my first approach, but because I’m not into Java or Python, I tried looking into Knime nodes only solution, but as I said, not an easy approach

Will check how you did it @takbb

[takbb]

Hi @bruno29a , yes I’d also tried looking at the KNIME looping, moving aggregations, lag columns and all the usual suspects! The stumbling block I had was the iterative nature of breaking up an individual cost across the different budget lines. It was “kind of” ok (though I never found a solution) if the cost as in the “Jessica” example had to split on two budget lines, but then what happens if we then find it needs to be split across three, or four… or… and that’s when I finally ditched that approach in favour of the first flow. (I couldn’t propertly make it work in the simple case and I had no idea how to make it work in the complex case).

I’d originally discarded the idea of the first flow on the basis that it was ok for an example, but I considered it possibly useless for “real world”, but then when I failed every other avenue, I went back to it just to see it could be done.

I’d also ignored the java snippet option because although I knew how it could easily do the cumulative aggregations required, I also knew that a java snippet cannot generate the additional rows that would be required when a cost splits across POs, and so was going to try python instead… and then it hit me, that what if the snippet only had to mark “already created” additional rows for deletion, and hence being supplied “all possible” rows by the Joiner became my solution.

[Rokank]

Thank you! I went ahead with the Java Snippet solution as I required two decimal places. I had to use basic Knime nodes where I needed to prepare a blanket PO for transactions with not enough budget. The solution works!!