Historical Stock Level from Current Stock and Transaction History

[williams596]

Hi there,

I have two tables, one detailing current stock levels and the other detailing stock transaction history. I would like to generate a script whereby I can view the stock level on any particular day in say the past 12 months.

Table1:

%_StockID	Current Stock Level
0001	10
0002	10
0003	10

Table2:

%_StockID	Movement Date	Movement Quantity
0001	28/04/2021	5
0001	27/04/2021	-10
0002	28/04/2021	-10

Desired Result:

%_StockID	Date	Stock Level
0001	29/04/2021	10
0001	28/04/2021	5
0001	27/04/2021	15
0002	29/04/2021	10
0002	28/04/2021	20
0002	27/04/2021	20
0003	29/04/2021	10
0003	28/04/2021	10
0003	27/04/2021	10

Hopefully that makes sense, let me know if any more info is required.

Thanks,

Will

[Dalton_Ruer · Accepted solution]

I have to run but at the end you can remove two of the columns from Calendar

StockData:
Load * Inline [
StockID, StockAmount
0001, 10
0002, 20
0003, 40
];

Movement:
Load * Inline [
StockID, Movement Date, Movement
0001, 28/04/2021, 5
0001, 23/04/2021, -10
0002, 28/04/2021, -10
0002, 26/04/2021, 17
0003, 25/04/2021, 15
0003, 24/04/2021, -17
];

Calendar:
Load * Inline [
CalDate
29/04/2021
28/04/2021
27/04/2021
26/04/2021
25/04/2021
24/04/2021
23/04/2021
];

// Add Every stock item to every day
Join (Calendar)
Load StockID
Resident StockData;

Join (Calendar)
Load RowNo() as CalendarKey,
CalDate, StockID
Resident Calendar order by StockID, CalDate DESC;

// Get the current stock level for the first day
Join (Calendar)
Load CalendarKey,
Lookup('StockAmount', 'StockID', StockID, 'StockData') as CurrentStockLevel
Resident Calendar
where CalDate = '29/04/2021';

// Now bring over all of the movement transactions
Join (Calendar)
Load "Movement Date" as CalDate,
StockID,
Movement
Resident Movement;

// We don't need the movement transactions anymore because they are useless now
drop table Movement;

Join (Calendar)
Load
CalendarKey,
IF(IsNull(CurrentStockLevel), Peek(NewStockLevel)+IF(IsNull(Movement), 0, Movement), CurrentStockLevel) as NewStockLevel
Resident Calendar
order by CalendarKey