community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!

Challenge #14: Warehouse Distribution

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.

 

UPDATE 2/29/2016 

It's leap day! Two solutions have been uploaded.

Creative Director
Creative Director

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

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.

Alteryx Partner

Sharing my solution here. :)

Alteryx Certified Partner

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


Aurora
Aurora

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)



Pulsar
 
Alteryx Certified Partner

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

 

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