Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Organizing work for a Warehouse Picker

a718
6 - Meteoroid

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:

SKUINITIAL DEMAND
1234100
246850
135725
987610



Currently, the items by warehouse location are as follows:

SKULOCATIONQUANTITY OF THAT SKU IN THAT LOCATION
1234A50
1234B40
1234C30
2468G25
2468H25
1357X10
1357Y8
1357DOCK-DOOR3

 

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)

SKUInitial DemandPick LocationTo Pick QuantityStatus
1234100A50Fully Met
1234100B40

Fully Met

1234100C10

Fully Met

246850G25

Equal Demand

246850H25

Equal Demand

135725X10

Partially Met

135725Y8

Partially Met

987610#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. 

3 REPLIES 3
DiganP
Alteryx Alumni (Retired)

@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! 

 

Dataset.PNG

Digan
Alteryx
a718
6 - Meteoroid

@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_IDPrioritySKUInitial_Demand
1A11234100
1A1246850
1A1135725
1A1987610
2B2123425

 

 

The output table would then look something like:
S
ince we prioritized order_id 1A first, we don't have enough of sku 1234 for order_Id 2B, so it becomes partially met

 

 

ORDER_IDPrioritySKUInitial_DemandPick_LocationTo_Pick_QuantityStatus
1A11234100A50Fully_Met
1A11234100B40Fully_Met
1A11234100C10Fully_Met
1A1246850G25Equal_Demand
1A1246850H25Equal_Demand
1A1135725X10Partially_Met
1A1135725Y8Partially_Met
1A1987610#N/Aor"NULL"#N/Aor"NULL"NO_Inv_To_Meet_Demand
2B2123425C20Partially_Met
DiganP
Alteryx Alumni (Retired)

@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. 

Digan
Alteryx
Labels