Batch macro needed?

[eaphymao]

I have a problem where I have multiple DC's to fill inventory to multiple stores. My input data looks like the below.

Input 1

DC	Style	Inv
1	Apple	10
2	Apple	5
3	Apple	3
4	Apple	2
5	Apple	10

Input 2

Store	Style	Need
1	Apple	7
2	Apple	6
3	Apple	4
4	Apple	3
5	Apple	1

and I want to get an output detailing the complete list of inventory from which DC to which stores. Something like the below. 

DC	Store	Fill
1	1	7
1	2	3
2	2	3
2	3	2
3	3	2
3	4	1
4	4	2
5	5	1

I can use a multi-row formula to solve the problem when I have a single DC, but after I exhaust the single DC how would I get Alteryx to use the next DC on my list to fulfill the remaining demand until all the demand is met? 

Output for a single DC using a multi-row formula

Store	Style	Need	DC	Inv	Fill	Remaining Inv	Remaining Need
1	Apple	7	1	10	7	3	0
2	Apple	6	1	3	3	0	0
3	Apple	4	1	0	0	0	4
4	Apple	3	1	0	0	0	3
5	Apple	1	1	0	0	0	1

I think I would need a batch macro, but how would I get it to consider the remaining need after using inventory from a previous DC? Am I even on the right track?

[LordNeilLord]

Hey @eaphymao

There is a weekly challenge that tries to solve this problem. There are many varied approaches that people took but have a look through some of the solutions provided and see if any give you a better direction. 

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-14-Warehouse-Distribution/m-p/36743

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[eaphymao]

Hey @LordNeilLord,

That's similar, but not quite far enough.

I'm able to solve the weekly challenge as is, however I have more than one central DC. I have multiple DC's. So i'm trying to figure out how I can have Alteryx automatically move onto the next DC and apply the allocation logic to fill remaining store demand after i've already used up the inventory in the first DC. 

Any help would be appreciated. Thank you!

[BenMoss]

Looks like you'll need an iterative macro which will allow you to remove stock allocation as you fill it.

Let me take a detailed look.

Ben

[BenMoss · Accepted solution]

See attached a solution. It may not be perfect but should be close!

Ben

[eaphymao]

Thank you so much! That's exactly what I was looking for. It really helps me understand how the macro works