Creating an weekly inventory level table from two datasets

106 Views Asked by At

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:

enter image description here

If not, what should be done?

my expected output enter image description here

0

There are 0 best solutions below