Load Script Help - Inventory Allocation

[Cory]

Our inventory team would like me to figure out the receipt date of the inventory we have in various locations.  Inventory can be in one of 4 locations - for the sake of simplicity we will call them location A, B, C and D.  Inventory will always go from A to B to C and finally to D where it is disbursed. We have a receiving table that tells us the date and quantity of the item that was received.  The data would look something like this:

Receiving:

Item	Receipt Date	Quantity
12345	4/15/2020	200
12345	2/10/2020	150
12345	1/5/2020	300
12345	12/16/2019	400
12345	11/15/2019	200
12345	10/1/2019	300
12345	8/15/2019	400

Inventory:

Item	Location	Quantity
12345	A	300
12345	B	200
12345	C	200
12345	D	600

The most recently received inventory is assumed to be in location A, once we have determined the receipt dates for all inventory in location A, we start assigning receipt dates to the inventory in location B, then location C and finally location D.  For the 300 items in location A, 200 were received on 4/15/2020 and the other 100 were received on 2/10/2020.  That would leave 50 from the receipt date of 2/10/2020 that would then be assigned to location B.  So for the 200 in location B, 50 were received on 2/10/2020 and the remaining 150 were from the next receipt date of 1/5/2020.

Ultimately, I should end up with something that looks like this:

Item	Location	Quantity	Receipt Date
12345	A	200	4/15/2020
12345	A	100	2/10/2020
12345	B	50	2/10/2020
12345	B	150	1/5/2020
12345	C	150	1/5/2020
12345	C	50	12/16/2019
12345	D	350	12/16/2019
12345	D	200	11/15/2019
12345	D	50	10/1/2019

Any help appreciated 

Cory

[javiersassen · Accepted solution]

It might not be the nicest solution, but it works! Took some time to figure it out..

Hope you can follow what I did:

Receiving:
LOAD * INLINE [
Item, Receipt Date, QuantityReceived
12345, 15-4-2020, 200
12345, 10-2-2020, 150
12345, 5-1-2020, 300
12345, 16-12-2019, 400
12345, 15-11-2019, 200
12345, 1-10-2019, 300
12345, 15-8-2019, 400];

Outer Join Receiving:
LOAD * INLINE [
Item, Location, QuantityInventory, QuantityInventoryPrior
12345, A, 300, 0
12345, B, 200, 300
12345, C, 200, 500
12345, D, 600, 700];

TempTable:
LOAD
*,
RangeMin(RangeMax(0,QuantityReceivedCumulative-QuantityInventoryPrior),QuantityReceivedTemp) as Quantity;
LOAD
*,
IF(QuantityInventoryPrior > QuantityReceivedCumulative,0,
IF(Location = Previous(Location),
RangeMax(0,QuantityInventoryPrior + QuantityInventory - Peek(QuantityReceivedCumulative)),
RangeMin(QuantityInventory,QuantityReceivedCumulative))) as QuantityReceivedTemp;
NoConcatenate LOAD
Item,
IF(Location = Previous(Location),Peek(QuantityReceivedCumulative) + QuantityReceived,QuantityReceived) as QuantityReceivedCumulative,
Location,
QuantityReceived,
QuantityInventory,
QuantityInventoryPrior,
[Receipt Date]
Resident Receiving
order by Location asc, [Receipt Date] desc;

Drop Table Receiving;

FinalResult:
NoConcatenate LOAD *
Resident TempTable
Where Quantity > 0;

Drop Table TempTable;

Thanks for the fun challenge!! Keep them coming haha

[Cory]

Sorry for the delay - priorities shifted and I am just now looking into this again.  I had to massage the data a little to get QuantityInventoryPrior and had to add some additional ordering to account for multiple Items, but I think I finally have it working!  THANK YOU!