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!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #14: Warehouse Distribution

GeneR
Alteryx Alumni (Retired)

Here is a new challenge for this week, it is a two part challenge so next week’s challenge will be a continuation.  The link to the solution for last week’s challenge is HERE

 

This week we are looking at a retail distribution analysis.  We need to allocate products from the warehouse to stores based on priority.  I have seen this challenge solved both with and without the use of an iterative macro.

 

The use case:

A retail chain has 25 stores carrying variety of items.  Not every store carries the same items and each has its own level of prioritization within the chain and different required stock levels.  There is a central warehouse that contains all of the available items.

 

The objective is to distribute items from the warehouse to each store, filling the available stock at each store in order of the store's priority.

 

Good luck, I look forward to hearing your feedback.  Thank you for playing along.

 

TaraM
Alteryx Alumni (Retired)

Two solutions for this one.

@GeneR says : The first is a solution without a macro and is in my opinion a more straight forward approach to solving the problem. I included the second macro approach because it is an excellent example of how to utilize an iterative macro.

Thanks!!

Tara McCoy
alex
11 - Bolide

I selected the iterator method and I also went back and put my old CFO hat on for this one. I had a bit of extra time so I went ahead and added branches to the workflow to calculate other stats that I would need to know if I ran the warehouse.

But first, since there were 215 rows to compare, I added a branch that automated the process to let me know if there was a row in my results that didn’t match the answer that was posted.

Then, I created

  1. a warehouse report that provides detail by product (Total Assigned, # Store Receiving Product, Total Remaining Product, etc.)
  2. a store report that provides data on total orders filled (what is going on in Houston?)
  3. an item report that provides data on the items that were left unfulfilled
  4. an expanded answer that includes columns for unfulfilled request amount, % assigned, etc.

 

The warehouse is totally out of 4 products and item 3 has the most stock remaining. Was too much of item 3 ordered or does it have a long lag time to receive product? The largest deficit was with items 6 and 9. Items 9, 1 and 3 were requested by the fewest stores while items 2 and 7 were requested by all but 1 location for each item.

All the extra data was created using just a few sort, join, filter, formula and summarize tools and goes a long way to provided value to the data.

 

wk14-1.PNG

wk14-2.PNG

warehouse

Wadim
5 - Atom

Yea nice challenge, but you uploaded a completly wrong file, "challenge_11_solution_2.yxzp ‏26535 KB" is a solution for a complete different challenge.

Don't get me wrong, I like those challenges but there is no challenge without misstakes.
This is really grinding my gears, I would like to see a iterative solution.

Cheers.

ankit_mandal
6 - Meteoroid

Sharing my solution here. :)

mceleavey
17 - Castor
17 - Castor

I didn't use the macro approach, instead using the running total and multi-row formula approach:

 

Spoiler
First I joined the data sets together on the key fields and sorted by item and priority.
I then created a running total of the "Required" column. I then amended the negative values in the running total column to be zero. Once I had this, I used a simple multi-row formula to say if the "Remaining" field was zero take the remaining figure from the row above, else take the "Required" value.

Challenge 14 Solution.PNG

Challenge 14 Results.PNG




Bulien

SeanAdams
17 - Castor
17 - Castor

Thank you for the challenge. 

@GeneR, @TaraM - would you mind taking a look at the macro solution provided - seems to be the solution to weekly challenge 11?

 

My solution attached - slightly different approach to the others:

 

Spoiler
Essentially there are 3 populations here:
- rows where they can be completely assigned within the stock levels available
- rows where we need partial assignment
- rows where we have nil assignment.

Approach:
-I used a running total followed by a formula to assign those products I could completely assign within stock levels.
-Then sorted filtered to show only the rows with 0 assigned 
-Then did a unique to only take the first row by priority
    Note: - by definition the first row will be partially assigned (the one returned by the unique); and all other rows for this product will be nil assigned.
    - Use a formula on the first row (the unique row) to get the partial assignment amount

Then union these 3 sets back together again.   Job done and super-fast (and no need to use multi-row formulas or macros)



estherb47
15 - Aurora
15 - Aurora
 
Laurap1228
11 - Bolide

I solved this with an Iterative Macro!  I know it was possible to solve this challenge without a macro, but I wanted to practice my macro building skills.

 

 

Spoiler
My macro:
challenge14Macro.jpg
My final workflow:
challenge14.jpg

 

NicoleJohnson
ACE Emeritus
ACE Emeritus

Second iterative macro = WAY better than the first! Although still feel a little like I'm throwing darts it. With a blindfold. :)

My solution attached, seems pretty similar to @Laurap1228's solution.

Spoiler
WeeklyChallenge14.JPG