Cross file validation with date range

Question

Hi

Lets see if I can explain what im trying to do.

I have a bounch of shipments, each shipment contains some basic data:

BookingDatepurchase_product_idpurchase_product_nameDeliveryCountryInvoice_WeightSales Priceshipment_AWB29-05-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7L30-05-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7M01-06-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7N02-06-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7O02-06-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7P

I want to add my cost price for each line, by comparing it to my purchase validation data:

SubCarrierNameSubCarrierIdAgreemtnStartDateAgreemtnEndDateProductIdCarrierProductTextUBCarrierProductTextMinWeightMaxWeightPickupCountryDeliveryCountryGLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business01 DKGLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business15 DKGLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business510 DKGLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business1015 DKGLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business1520 DKGLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business2025 DKGLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business2530 DK

So basicly I wanna add a cost price.

So from the first dataset the BookingDate has to be between the AgreementStartDate and AgreementEndDate.
The InvoiceWeight has to be between MinWeight and MaxWeight

And delivery country and productid has to match.

if I do a simple join on delivery country and product id I can do the match. But im missing the weight/and date validation?

BookingCostValidation.yxzp

Qiu · Accepted Answer

@Hamder83 
I dont quite understand your business flow. so just try to follow you approach.

1. Generate days between  the AgreementStartDate and AgreementEndDate.so csn match with BookingDate

2. use a filter to judge if the InvoiceWeight has to be between MinWeight and MaxWeight

Let me know what you think.