Hello,
As a supply chain engineer, I am have difficulties developing a model where I would input the quantities to be ordered, the product minimum ordering quantities (MOQ) and the rounding quantities and the model should adjust the quantities to be ordered in the following manner:
P.S: Attached is excel sheet with example of the desired outputs.
1- If the quantities to be ordered in a month n = 0 => model should leave the quantity as it is.
2- If the quantities to be ordered in month n < MOQ => the model should add quantities to be ordered from the following months n+1, n+2… until it reaches MOQ and the rounding. While, substracting the quantities that were moved to previous month.
Example:
Month N => quantity to be ordered = 2 and MOQ is 3 and the rounding is 1
Month N+1 => quantity to be ordered = 1 and MOQ is 3 and the rounding is 1
The model should output:
Month N => quantity to be ordered = 3
Month N+1 => quantity to be ordered = 0
3- If the quantities to be ordered > MOQ and the quantities is rounded to the rounding factor => model should leave it as it is.
4- If the quantities to be ordered > MOQ BUT the quantities are rounded to the rounding factor => model should keep adding the months need from the next months until it reaches the rounding. While, substracting the quantities that were moved to previous month.
Example:
Month N => quantity to be ordered = 4 and MOQ is 3 and the rounding is 3
Month N+1 => quantity to be ordered = 5 and MOQ is 3 and the rounding is 3
The model should output:
Month N => quantity to be ordered = 6
Month N+1 => quantity to be ordered = 3
P.S: Attached is excel sheet with example of the desired outputs. Product MOQ.xlsx (11.9 KB)
I think I understand rounding after trying to solve this problem by hand. I added two new columns: “Actually ordered” which should match your Quantities Adjusted to MOQ but it doesn’t always match (red cells). Could you check the red cells, I wonder if I made a mistake in the logic.
Then I added a second column called “overflow” which represents inventory that was ordered by necessity but was not needed by customer (actually ordered - demand (Quantities to be ordered))
Finally, with modeling there is a need to predict (usually). So in this case we are not predicting, but using actual values to inform our decision. In that case what you would like is a formula? As in you give the workflow the columns you provided me and it will generate the “actually ordered” or “Quantities Adjusted to MOQ”, correct?
If you’d like to make predictions with a machine learning model, you can see this post and workflow.
Hello @victor_palacios ,
Thank you for getting back to me. For the rounding quantity it means that the quantities to be ordered need to be a multiple of that rounding quantity.
For the red Columns, after verification your data is correct and I actually made a mistake in the data but your rectification is correct and you are right, you understood the logic very well.
Furthermore, I couldn’t do a workflow yet since I found it a little bit advance for my knime knowledge.
Please let me know if you have any further questions. Looking forward to hearing back from you.
Ok, and it is safe to say you don’t want to predict, you simply want an output column? I’d need to know exactly what input you get, when you get it, and what output you want.
If your excel sheet is the input, I can show you how to generate the output from my two columns in KNIME.
From above:
with modeling there is a need to predict (usually). So in this case we are not predicting, but using actual values to inform our decision. In that case what you would like is a formula? As in you give the workflow the columns you provided me and it will generate the “actually ordered” or “Quantities Adjusted to MOQ”, correct?
If you’d like to make predictions with a machine learning model, you can see this post and workflow.
Yes @victor_palacios, exactly. Just like you said, my current need is the following:
The inputs are the quantities to be ordered, MOQ and the rounding quantity; and the workflow should generate the quantities adjusted to MOQ or actually ordered following that logic.
Thank you for your time and patience,
Looking forward to hearing back from you.
@victor_palacios could you please show me how to how to generate the output “actually ordered” from the two columns quantitites to be ordered, product MOQ and Rounding quantity in KNIME, because I have difficulties designing the workflow.
Thank you in advance
Hello, I’m running into the same issue. Would you mind if I use the Python Script node? (This requires you to have python installed on your computer, but doing so with knime is very very easy). If python is ok, I should have a solution by the end of the week.
Hello @victor_palacios , I would prefer a solution without the Python Script node if possible, because this workflow will be used by all my procurement team so I will have to go through a long IT process to get them install python in all the departments’ laptopts.
However, if we can’t do without Pyton Script node it’s okay than we can go with Phyton Script node.
I went with Victors approach of keeping track in an overflow column, but I think this problem is better solved with an inventory column.
I’m not sure my base-nodes-only implementation is the most optimal one. The main challenge has been that we need the result of the previous row for the calculation, without changing it. This has been solved with a recursive loop that passes the data table around several times, while single-ing out one row for the calculation.
Preprocessing and Group Loop Start (to process different products one after another)
Recursive Loop Start
Lag the Overflow column so we can reference it in the calculation
Row Splitter to get one row for calculation. Controlled by Flow Variables (currentIteration equals rowNumber)
do the calculations (see Java Snippet)
Sort the table chronologically (Concatenate only smacks tables together top to bottom)
Feed table back to start (bottom input of Recursive Loop End)
A Row Filter breaks the loop execution after the last row has been processed
Now, using nested loops as bad. I optimised the WF using a Java Snippet node, because this node is made for that kind of problem: It can “access” the previous rows by storing them in global variables. By doing so, we now only have to do the math behind the problem and nothing else:
If enough overflow from the previous row is left, don’t order anything; Else continue with step 2:
order as much as requested, minus the previous overflow
increase that to the minimum order amount
increase that to the next integer muliple of the rounding quantity
With a minor adjustment, it can also do without the Group Loop, so a single node is all we need You can use whatever you like, but the nested loops will be significantly slower on bigger input tables.
PS: The logic behind the calculations is exactly the same in all implementations. Additionally, the minimum order quantity doesn’t have to be a integer multiple of the rounding quantity.
@mehdi_zinbi , @Thyme’s solution is exactly what I was working on (except I was using Python).
I also agree that because you need to “access” a variable, code is better for this particular problem. I would use the first 4 nodes and then the Java Snippet node (for a total of 5 nodes) to solve this problem. Java Snippet also doesn’t require extra downloads like Python so it’s ideal.
Following the product MOQ the next step I have and that I am stuck in is to adjust the quantities to be ordered to the supplier MOQ in the following manner:
P.S: Attached is excel sheet with example of the desired outputs. Supplier MOQ.xlsx (16.5 KB)
Step 1: convert the quantities to be ordered to the supplier MOQ unit (to go from quantity in item to quantity in pallets)
Step 2: Group the quantities to be ordered in MOQ unit by month by supplier.
Step 3: Adjustements (this is the part where I am stuck:
1- If the sum of the quantities to be ordered in MOQ unit in a month n = 0 or the sum of the quantities to be ordered in MOQ unit > MOQ => model should leave the quantity as it is.
2- If the sum of the quantities to be ordered in MOQ unit in a month n < MOQ => the model should add quantities to be ordered from the following months n+1, n+2… until it reaches the first value that is higher MOQ (not exact MOQ like what we did with the product MOQ). While, substracting the quantities that were moved to previous month.
Example:
Month N => sum of the quantities to be ordered in MOQ unit = 2 and MOQ is 3
Month N+1 => sum of the quantities to be ordered in MOQ unit = 2 and MOQ is 3
Month N+2 => sum of the quantities to be ordered in MOQ unit = 3 and MOQ is 3
Month N+3 => sum of the quantities to be ordered in MOQ unit = 5 and MOQ is 3
The model should output:
Month N => quantity to be ordered = 4
Month N+1 => quantity to be ordered = 0
Month N+1 => quantity to be ordered = 3
Month N+1 => quantity to be ordered = 5
So in here there is no rounding quantity and the sum of quantities to be ordered don’t need to be exactly equal to MOQ but rather the first closest value when adding the following months.
P.S: Attached is excel sheet with example of the desired outputs.
hi @mehdi_zinbi ,
I’ve tried to solve this problem with Knime nodes, but I couldn’t managed it, so I had to use Python.
This is my workflow, hope it helps KNIME_temp.knwf (29.1 KB)
Ordering half pallets is ok? Just asking, because the initial problem was to round up to certain values.
What should be do with months at the end, whose orders aren’t big enough? I assumed to simply leave them there.
Since the pallet size is constant within a supplier, I mixed up the order to optimise the workflow. I used items_to_be_ordered because it requires less preprocessing, but using pallets_to_be_ordered would work the same way.
The central idea was to have the information of each month+supplier in a single row. The Java Snippet would then assign an index to identify each order, while keeping track of the current order amount. Once the order is big enough, the memory is reset and a new order is started. The GroupBy and Joiner nodes control the information flow to and from the Java Snippet (compress, guide, distribute). After that the problem is pretty much solved, only needs a Missing Value to spread the results everywhere.
@Thyme I made the same assumption about the ending months. I think that half pallets are ok, because trying to “round” them would mean to adjust the quantities of the products ordered accordingly, but the quantities ordered must be integers.
You can use whatever you like, but the nested loops will be significantly slower on bigger input tables.
