I have two dataset, one with inventory data, like this
Item Number Date Inventory
0 064-01-1347 06/17/2023 3561
1 064-01-1361 06/17/2023 4035
2 064-01-1375 06/17/2023 2684
3 064-01-1385 06/17/2023 5136
4 064-01-1399 06/17/2023 6399
and another with inventory data like this,
Item Number Inventory Receive Total Item Qty
0 064-13-0002 07/07/2023 700
1 064-13-0338 07/07/2023 1700
2 064-13-0340 07/07/2023 1000
3 064-13-0353 07/07/2023 1900
4 064-13-0416 07/07/2023 1000
I have to make a table based on Item Number and weekly dates inventory level for each item, how to do that?
The two datasets have details of several items that gets delivered on various days over a period of 3 months, but each item has a different delivery date, so grouping and summation over unique items don't really give a straight result to be converted into a pivot table, as i get a lot of null values,
The output table should be of this format:
If not, what should be done?
my expected output enter image description here