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?
