Hello fellow Alteryx fans! I have a listing of inventory against which I'd like to apply a separate listing of outgoing shipments. The inventory, however, may have multiple rows, since each batch of inventory received may have a different excise tax rate applied to it. I'd like to apply these shipments against the inventory on a First-In-First-Out basis.
So, if inventory batch #1 contains 100 units and inventory batch #2 contains 50 units, but then I ship out 125 units, I want the workflow to show me there are 25 units of batch #2 remaining AND I want to see that we consumed 100 units of batch #1 and 25 units of batch #2. Similarly, if only 50 units were shipped out, I want to see an ending inventory of 50 units for batch #1 and 50 units for batch #2, as well as 50 units consumed of batch #1. There could be many many batches, so I need this solution to not include Multi-row formulas that assume we would only have a maximum of x number of batches.
I KNOW the answer must be in using a combination of Running Total and Multi-row tools, but I cannot seem to figure it out. Sample files of receipts and shipments are attached to this query.
Thank you in advance!
