Complex script to calculate goods origin

[micheledenardi]

I have to write a script to calculate the WarehouseNo from which delivered goods are coming from.

The data structure is this:

TabWarehouse:

WarehouseNo	Product	QuantityAvailable
1	A	100
2	A	50
3	A	200
4	A	500

Delivery:

Delivery	Product	DlvQuantity
1	A	50
2	A	100
3	A	200
4	A	50
5	A	300

I don't have a direct link between deliveries and warehouses so i have to calculate a new field in the delivery table where i want to store the WarehouseNo from which delivered goods are coming from.

For example, if we have 5 deliveries:

- Delivery 1 picks 50pcs from warehouse 1. Now warehouse 1 has only 50pcs available for the next delivery.

- Delivery 2 picks 50pcs from warehouse 1 and 50 from warehouse 2. Now warehouses 1 and 2 are empty.

- Delivery 3 picks 200pcs from warehouse 3. Now warehouse 3 is empty.

- Delivery 4 pick 50pcs from warehouse 4. Now warehouse 4 has 450pcs available for the next delivery.

- Delivery 5 picks 300pcs from warehouse 4. Now warehouse 4 has 150pcs available for the next delivery.

To obtain this final table

Delivery	Product	DlvQuantity	WarehouseNo
1	A	50	1
2	A	100	1 & 2
3	A	200	3
4	A	50	4
5	A	300	4

[petter]

I think that you have presented a simplified use case - right? How many warehouses would you have per product in your real use case? And how many products? I guess dates would also be involved here?

[micheledenardi]

Yes, this is very simplified use case.

We have hundeds of warehouses and hundreds of thousands of products.

We can add a delivery date to the delivery so we can establish an order to process it.

[petter]

Good to know - so any proposed approach needs to be scalable to that number of warehouses and products....

[petter]

What would be the maximum number of deliveries for a single product for a single date. How many warehouses would be the maxium number that has to be delivered from?

[avkeep01]

Hi Michele de Nardi,

I made a start on a script. Too bad I don't have the time to finish it today. Only the 4th iteration is going wrong it still delivers -400 instead of 50. Maybe someone else can work further on my script.

For this test case it runs fast enough, not quite sure wat the problems will be running hundreds of warehouses and products. Gonna take a lot of time to reload I suppose.

Maybe it helps you (or somebody else) to get this difficult task done.

I've attached an example.

[micheledenardi]

The maximum number of deliveries for a single product for a single date would be... hundreds.

The maximum number of warehouses which a single product can be taken... maybe ten.

[avkeep01 · Accepted solution]

Hi Michele De Nardi,

I fixed the script. It runs the deliveries through the warehouses. I added comments in the script to make it better to understand.

basically;

Get the values to be delivered, join them to first warehouse in the sequence. Deliver all possible quantities. That generates a new table with new to deliver quantities and available quantities. This is done in a loop untill all deliveries are fullfilled.

[avkeep01]

Small update: when the deliveries can be totally fullfilled an error occures because no new deliveries are calculated. This is fixed in the attached file.