We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
alteryx Community

# Weekly Challenge

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

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

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

## Challenge #65: Fulfilling Inventory Orders

8 - Asteroid

This one was quite fun! Great little brain teaser here thanks @JoeM

Having solved it this way ... I wonder if there's a more Pythonic way to do this?

Because isn't this a Linear Programming problem?

This warrants more investigation!

UPDATE: also! I was reading through the "Make Group" tool mastery entry and realised that the "Make Group" tool might've been able to simplify my workflow by not requiring. But still needs further meditation.

Spoiler

This one has 3 steps, Prep it! Ship it! and Report it!

1. Prep it

First we need to construct our view of inventory, Joined to the orders. 3 tools and done, I bet you my Excel buddies are jealous!

2. Ship it

Figure out which of the orders we are going to fulfil, I'm using a Boolean flag, initially set all to "True" and then a series of Multi-row formula to solve.

I had to synthesise an index by joining the text of the Order SKU and the Order quantity. Sometimes when you can't solve an analytics challenge with the raw data, you need to build a scaffold and synthesise the data you need.

If I had the facility to "Group by" the SKU's and the Order ID's in the Multi-row Formula tool, I wouldn't need the scaffold.

3. Report it

Now we filter out the rows that we don't want,
and build a cumulative consumption column.

For this one, I output the results to Excel cos I wanted to compare my results with the solution's results as I had messed up one of the formula's earlier which is below here. The first time I did this I didn't make the conditions mutually exclusive / collectively exhaustive (MECE) enough, because I had a <= symbol instead of a < symbol. Small things! I tell ya!

IF

[Order Quantity] < [Quantity in Stock] AND [Row-1:Order SKU]!=[Order SKU]

THEN
-[Order Quantity]

ELSEIF
[Row-1:Drawdown]-[Order Quantity]+[Quantity in Stock] < 0 AND [Row-1:Batch]=[Batch]

THEN
[Row-1:Drawdown]

ELSEIF
[Row-1:Drawdown]-[Order Quantity]+[Quantity in Stock] >= 0
THEN
[Row-1:Drawdown]-[Order Quantity]

ELSE
[Row-1:Drawdown]
ENDIF

7 - Meteor

Solution Attached.

Spoiler
Multi-Row for the win!

8 - Asteroid

This one nearly broke me. Multi-Row is definitely a bit of a blind spot--spent way too long trying to come up with a solution that didn't use it.

Spoiler
6 - Meteoroid

Solution attached.

Spoiler
Multi-Row Insanity

11 - Bolide
Spoiler

7 - Meteor
Spoiler

12 - Quasar

Definitely some extra steps but it was all I could think of right now. I would have been in trouble without the multi-row tool.

8 - Asteroid

Solution:

Spoiler
8 - Asteroid
Spoiler

Not sure if anyone else has done it like this, but managed with just one formula. Tripled checked results and seems ok.

if [Order SKU]!=[Row-1:Order SKU] then [Supply]-[Demand]

elseif [Order SKU]=[Row-1:Order SKU] and [Row-1:Remaining]-[Demand]>0 and [Row-1:Remaining]>[Demand]then [Row-1:Remaining]-[Demand]

elseif [Order SKU]=[Row-1:Order SKU] and [Row-1:Remaining]-[Demand]<0 then [Row-1:Remaining]

else [Row-1:Remaining]-[Demand] endif

7 - Meteor
Spoiler