How to place scanned parcel to a truck based on scan date.

[Hamder83]

Hi 

I know this is a lot, and maybe hard to understand. But any help will be appriciated.
I'm working in a transport company, and I am building a model, which is suppose to place scanned parcels on a linehaul truck.

The problem is:
I know how many linehauls se send, when they are send and when they arrive at the lastmile carrier.
And I know how many pallet is on each linehaul.

From the lastmile carrier I know how many parcels they scan every day.

So lets say I send a truck from :
Truck 1 out of 2
VeichleID : WE5V014
Germany to portugal
with 18 pallets. Arrving (ADA - Actual day of arrival) he 2022-07-11

Truck 2 out of 2
VeichleID : WND5793A
Germany to portugal
with 18 pallets. Arrving the 2022-07-11

Then I send another truck:
Truck 2
VeichleID : RZ771AH
Germany to portugal
with 7 pallets. Arrving (ADA - Actual day of arrival) the 2022-07-14

Then I send another truck:
Truck 3
VeichleID : TEST
Germany to portugal
with 10 pallets. Arrving (ADA - Actual day of arrival) the 2022-07-16

Then all parcels scanned between the 2022-07-11-> 2022-07-13 (the day before the next truck) belongs to truck 1 (if only one). (We can have more than 1 truck arriving the same day)

Then all parcels scanned between the 2022-07-14-> 2022-07-15 (the day before the next truck) belongs to truck 2 and so on.

Now I want join my lastmile records to the correct linehaul lane.

So I generate a theoritical split, with the quantity of scans on a lane per scanday. And divide that with the amount of pallet delivered.

Know I want to take my calculated avg of parcels per pallet and calculate how many parcels were on each veichle.
The 2022-07-11 we had 228.21 parcels per pallet.

I want to take Pallet_ACT * Calc_Avg_Parcel_PLL (6*228.21) = 1369 (so the first 1369 records from right needs to be assigned VEICHEL_ID (B 601 DMC)
And again, 2. row is 15*228,21 = 3423 so from 1369 to 3423 is VEICHEL_ID (AG 23 AWS)


Does this make any sense to any other than me?

[Hamder83]

Sample workflow attached