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 #65: Fulfilling Inventory Orders

izamryan
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
izamryan_2-1590429673069.png

 


 


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

 

eric_strein
8 - Asteroid

Solution Attached.

 

Spoiler
Multi-Row for the win!

eric_strein_0-1590610155098.png

 

jtwmoore
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
065_solution_image_JTM.PNG
jcollake
6 - Meteoroid

Solution attached.

 

Spoiler
Multi-Row Insanity
jcollake_0-1590675738549.png

 

deviseetharaman
11 - Bolide
Spoiler
 
MarielJinang14
7 - Meteor
Spoiler
MarielJinang14_0-1590858858175.png

 

phottovy
13 - Pulsar
13 - Pulsar

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.

liamholland
8 - Asteroid

Solution:

Spoiler
Solution Screenshot.png
brig
8 - Asteroid
Spoiler
image.png

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

wincenzo
7 - Meteor
Spoiler
wincenzo_0-1592053526068.png