topic Re: Challenge #65: Fulfilling Inventory Orders in Weekly Challenges

Challenge #65: Fulfilling Inventory Orders

JoeM — Tue, 13 Sep 2022 19:11:45 GMT

Last week's challenge can be found HERE!

You have been given some order information that contains a list of orders with SKUs and related quantities. Additionally, you have a data set with the total inventory for each SKU.

You have been tasked with figuring out how to consume all of the inventory with the least amount of orders. In other words, you have been asked to fill the largest orders for each SKU in descending order.

Additionally, only complete orders may be fulfilled. For example, if an order calls for 4 units of a SKU, and only 3 are left in inventory, you can't fulfill the order.

Re: Challenge #65: Fulfilling Inventory Orders

SeanAdams — Mon, 10 Apr 2017 09:05:59 GMT

nice one!

this solution uses a basic "Fill in reverse size order" principle - but this doesn't guarantee maximum packing - with different order data, we may need to add an iterative macro or some other Knapsack process to maximize fill.

But for this input data, it matches the provided output.

Re: Challenge #65: Fulfilling Inventory Orders

BenMoss — Mon, 10 Apr 2017 14:23:45 GMT

Here's my solution :)

Loved that you made it sound so easy before the complete orders only caveat ;)

Re: Challenge #65: Fulfilling Inventory Orders

NicoleJohnson — Mon, 10 Apr 2017 14:37:17 GMT

My solution.

Re: Challenge #65: Fulfilling Inventory Orders

JoeM — Mon, 10 Apr 2017 17:39:26 GMT

@BenMoss, I have to do at least one caveat. ;)

Re: Challenge #65: Fulfilling Inventory Orders

toddhowl — Mon, 10 Apr 2017 20:03:43 GMT

Had to do this one twice. The first time through I had way too many tools, so tried it again with fewer. :-)

Re: Challenge #65: Fulfilling Inventory Orders

patrick_digan — Mon, 10 Apr 2017 20:11:57 GMT

I saw that many people have already provided similar looking solutions, so I decided to go down the iterative macro route that @SeanAdams mentioned. While I've created a macro that gets the correct answer and will allow for some more flexibility, it still has some major gaps. The iterative macro can be tweaked to find all combinations by unchecking the box (and then you could figure out the smallest set for sure), but it quickly blows up the processing because I haven't optimized it yet. It's not very well laid out, but here is my basic logic:

You sort the orders from largest to smallest for a given SKU. You put a running total to get the sum as you keep adding up all the orders. You compare the running total to the needed quantity, and there are 3 basic things that can happen: 1) You find a 0 on some line which indicates that you've found a combination that fulfills the request. You want to spit this out of the iterative macro as a match. 2) You find that the running total at some point is greater than the needed quantity. In this case, you want to remove the offending number, and try again. 3) You find that the running total never reaches the needed quantity. In this case, you want to "go backwards" and add back all numbers that are lower than the smallest number in your current set. It's the combination of 2 and 3 above in an iterative macro that would "eventually" find all the possible combinations so you could ensure that the smallest set was chosen. It would take way too many iterations the way it is currently setup to answer this for sure. Luckily the sample data matches the first match in my algorithm :)

My multirow is a bit crazy, I think it will be interestin...

MinaGO — Tue, 11 Apr 2017 08:51:17 GMT

My multirow is a bit crazy, I think it will be interesting to see what other people did. This was definitely a fun one! I never usually get to use multirow in such a way. Thanks Joe :)

Re: Challenge #65: Fulfilling Inventory Orders

Simona — Tue, 11 Apr 2017 16:19:50 GMT

I struggled a bit to be honest as I did not realize that the sorting was essential before the multirow, so I kept having more records and I was not sure why. Then I read more carefully the description of the challenge and I realised my mistake. Very good one!

It was a good exercise, these challenges are so neat and definitely I learnt a new technique once again! :)

Re: My multirow is a bit crazy, I think it will be interestin...

Simona — Wed, 12 Apr 2017 08:42:53 GMT

Mina, I just downloaded your workflow and looked at your multirow formula. I did kind of similar thing but I used first a multirow to compute the cumulative/ running total, then a formula to calculate the difference. I like that you did the same just using the multirow formula!

Actually, you could also simplify a little bit your Multirow, as the last IF statement is not needed and you could substitute it with [Row-1:Remaining], so your calc would look like:

(IF [Row-1:Order SKU]=[Order SKU] THEN [Row-1:Remaining] ELSE [Supply] ENDIF)-[Demand]>=0

THEN ((IF [Row-1:Order SKU]=[Order SKU] THEN [Row-1:Remaining] ELSE [Supply] ENDIF)-[Demand]) ELSE

[Row-1:Remaining]

ENDIF

Re: My multirow is a bit crazy, I think it will be interestin...

MinaGO — Wed, 12 Apr 2017 09:12:10 GMT

Yes this is a nice little reduction to get the solution in a slightly shorter way. Thanks Simo :)

There may still be an argument for leaving it as the original in a real use case where there are only orders which outstrip supply on certain SKU's and this would cause a problem.

Re: My multirow is a bit crazy, I think it will be interestin...

Simona — Wed, 12 Apr 2017 09:29:58 GMT

Absolutely, it makes sense to me what you are saying, in that case you would need that. :)

Cheers Mina!

Re: Challenge #65: Fulfilling Inventory Orders

DinoF — Wed, 12 Apr 2017 11:26:22 GMT

Hi here's my solution, it might not be the quickest and simplest one but at least it works.

Cheers

Re: My multirow is a bit crazy, I think it will be interestin...

alex — Thu, 13 Apr 2017 02:06:09 GMT

@Simonaand @MinaGO - You can also use the group by option inside the multi-row tool so that the formula resets for each the item selected, in this case it would be Order SKU.

Re: Challenge #65: Fulfilling Inventory Orders

alex — Thu, 13 Apr 2017 02:03:43 GMT

Re: My multirow is a bit crazy, I think it will be interestin...

MinaGO — Thu, 13 Apr 2017 08:05:55 GMT

Edit: Nevermind your version is using group by my alteryx is crazy and when I download and open yours it wipes out all the group bys. Thank you very much for the feedback!

Re: My multirow is a bit crazy, I think it will be interestin...

Simona — Thu, 13 Apr 2017 07:57:54 GMT

Alex, I downloaded your workflow, very neat!! :) To be honest I did not use that much before the option "group by"in the multirow formula, but I can see now why I should have used it more!! Thank you :)

Re: Challenge #65: Fulfilling Inventory Orders

MarqueeCrew — Sat, 15 Apr 2017 03:35:53 GMT

Joe,

While I solved the problem with more tools than some, I needed some extra tools because instead of a join I used find replace. Nesting logic in a multi-row formula could remove another tool, but once you're there and getting good results I opted for maintainability. Kudos to all.

Happy Easter,

Mark

Re: Challenge #65: Fulfilling Inventory Orders

Natasha — Sun, 16 Apr 2017 18:47:06 GMT

JoeM,

Thank you for another interesting challenge, had to revisit my logic a few times.

I hadn't used many tools, though my multi-row formula is rather lengthy, as I wanted to check whether the first order can be fulfilled or not.

Re: Challenge #65: Fulfilling Inventory Orders

Natasha — Mon, 17 Apr 2017 16:57:25 GMT

double post