A company has 50 projects. Each project has a project amount.
Company has a list of suppliers.
For any given supplier, the company has a threshold limit on the overall project amount that the supplier can handle called ‘Threshold’.
The total amount of projects that a supplier is associated should be less than the threshold value.
The goal is to allocate the projects to suppliers.
The number of projects can increase or decrease as well as the threshold value can change.
The data file ‘project-supplier-distribution.xlsx’ is attached. The excel file has 4 tabs:
threshold - overall sum of project amount per supplier (threshold) limit
suppliers - list of suppliers
project - the list of project, the project amounts. For each project, we need to find out the allotted supplier, cumulative project amount and total amount at the supplier level.
Sample-Result-Threshold-500 - The tab contains the solution for a threshold value of 500. In the tab, I have illustrated a possible loop logic to derive the result but not able to put it in Knime.
My guess is that we need to use:
Chunk loop to process each project
Chunk loop to allot each supplier
Recursive loop to compute and track the overall cumulative project value, assign the supplier and change the supplier once the threshold value is reached.
Flow variables for threshold value, current supplier, and cumulative project value
Flow variable Node or Widget to change the threshold value
This solution assumes that the order the suppliers appear in the spreadsheet source is the order of preference… i.e. first supplier gets projects allocated first, then second etc. Threshold is also the same across all suppliers.
Coming from finance background, still getting around the concept of recursion but what I did manually in the excel sheet, the solution is exactly doing that.
Couple of thoughts:
Would the solution work if the Projects table is very very big? My guess is that the recursion node keeps on accumulating the data till the input table at recursion loop end gets exhausted.
There’s hope - I come from a finance background as well and am a self-taught programmer - once it clicks, it clicks
The good thing about this solution is that it depends on more on the number of suppliers and the threshold and does not process line by line… that said yes as with any loop if you need to process large amounts of data it may be less performant.
When you have more than 100 suppliers you may have to increase the max number of iterations manually or get a supplier count, turn it into a flow variable, and use it to set the max number of iterations for the loop.
Given that we need to identify when the threshold is exceed I can’t see a way to do this without a loop.
I think there are ways to use Java Script via column expressions as a workaround, but not too familiar with that to be honest…
