Greetings Alteryx Community,
I'm trying to develop a workflow that creates an organized to-do list for a warehouse picker based on how much of what sku is demanded and where that sku is located in our warehouse.
Situation:
I want 100 units of sku 1234,
50 units of sku 2468,
25 units of sku 1357,
and 10 units of sku 9876
In other words, my initial demand table looks like this:
SKU | INITIAL DEMAND |
1234 | 100 |
2468 | 50 |
1357 | 25 |
9876 | 10 |
Currently, the items by warehouse location are as follows:
SKU | LOCATION | QUANTITY OF THAT SKU IN THAT LOCATION |
1234 | A | 50 |
1234 | B | 40 |
1234 | C | 30 |
2468 | G | 25 |
2468 | H | 25 |
1357 | X | 10 |
1357 | Y | 8 |
1357 | DOCK-DOOR | 3 |
As you can see, there is more than enough of sku 1234 in stock to meet demand, just the right amount of stock of sku 2468 to meet demand, not enough of sku 1357 to meet demand. (In the case of 1357, I still want as much as possible), and no stock of sku 9876 to meet demand.
Also there is a business rule, where we can only pick from letter locations, not DOCK-DOOR locations, so they must be ignored. (This is because they're still being received, and haven't been put away into a proper, letter location yet).
My desired output is as follows. It's important that the "To Pick Quantity" = the initial demand if there is sufficient stock (we don't want to over-pick), and as much as possible if the current stock level is less than the initial demand. Also, if there's no stock to meet a demand, that should be included as well (I've also included a status column, which denotes all this)
SKU | Initial Demand | Pick Location | To Pick Quantity | Status |
1234 | 100 | A | 50 | Fully Met |
1234 | 100 | B | 40 | Fully Met |
1234 | 100 | C | 10 | Fully Met |
2468 | 50 | G | 25 | Equal Demand |
2468 | 50 | H | 25 | Equal Demand |
1357 | 25 | X | 10 | Partially Met |
1357 | 25 | Y | 8 | Partially Met |
9876 | 10 | #N/A or "NULL" | #N/A or "NULL" | NO Inv To Meet Demand |
How may I obtain this output? This is a task that has baffled me in excel, but I feel that since I'm utilizing Alteryx for some other tasks, perhaps it can assist here as well. My current process is in excel, going 1 sku at a time, matching, creating this list... There's definitely a better way to do this. Maybe even a Macro? (I have very little experience with Macros).
Any assistance would be appreciated; and I'm gland to expound any point if necessary.
Solved! Go to Solution.
@a718 Attached is a workflow that creates the output you wanted. There are a couple of steps but I documented it along the way. Let me know if you need further explanation!
@DiganP
Amazing!!!
One Question though to improve the process in case it comes up: Since this is will run for one "sales order" at a time, how can I do this for multiple orders? (Perhaps introducing a priority would help to make sense of which order to run this process for first?)
Example of the new input:
Order_ID | Priority | SKU | Initial_Demand |
1A | 1 | 1234 | 100 |
1A | 1 | 2468 | 50 |
1A | 1 | 1357 | 25 |
1A | 1 | 9876 | 10 |
2B | 2 | 1234 | 25 |
The output table would then look something like:
Since we prioritized order_id 1A first, we don't have enough of sku 1234 for order_Id 2B, so it becomes partially met
ORDER_ID | Priority | SKU | Initial_Demand | Pick_Location | To_Pick_Quantity | Status |
1A | 1 | 1234 | 100 | A | 50 | Fully_Met |
1A | 1 | 1234 | 100 | B | 40 | Fully_Met |
1A | 1 | 1234 | 100 | C | 10 | Fully_Met |
1A | 1 | 2468 | 50 | G | 25 | Equal_Demand |
1A | 1 | 2468 | 50 | H | 25 | Equal_Demand |
1A | 1 | 1357 | 25 | X | 10 | Partially_Met |
1A | 1 | 1357 | 25 | Y | 8 | Partially_Met |
1A | 1 | 9876 | 10 | #N/Aor"NULL" | #N/Aor"NULL" | NO_Inv_To_Meet_Demand |
2B | 2 | 1234 | 25 | C | 20 | Partially_Met |
@a718 If you want to run 1A first then 2B, you can use the sort tool after the input data tool to sort on numerical order.