I hope someone can help me out with a particular problem. After several days processing data in a matrix form i am now stucked with it.
We have multiple products within multiple storages, and a list of orders, when (on which day) we have to deliver these products.
The warehousing is working with FIFO principle. If the oldest storage is out stock, we pick from the next later storage and so on.
I have to calculate now, from how many storage does a single product of one order come from. I have tried working with tranpose, crosstab and multiple rows, multiple fields but haven't successed jet. Below you can see the simulated datas. Ofcouse we have more then 6 storages and much more than 2 products.
Warehose Data
| Object-Nr. | Storage place | Date of receipt | Stock |
| 1111 | A1 | 01.01.2020 | 100 |
| 1111 | A2 | 02.01.2020 | 100 |
| 1111 | A3 | 03.01.2020 | 500 |
| 1111 | A4 | 04.01.2020 | 300 |
| 1112 | A5 | 01.01.2020 | 350 |
| 1112 | A6 | 01.01.2020 | 200 |
Order list
| Object-Nr. | Date of delivery | Delivery amount |
| 1111 | 10.10.2020 | 320 |
| 1111 | 11.10.2020 | 50 |
| 1111 | 12.10.2020 | 280 |
| 1112 | 10.10.2020 | 50 |
| 1112 | 11.10.2020 | 100 |
| 1112 | 12.10.2020 | 50 |