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.
