Cross file validation with date range

[Hamder83]

Hi 

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

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

BookingDate	purchase_product_id	purchase_product_name	DeliveryCountry	Invoice_Weight	Sales Price	shipment_AWB
29-05-2020	1000000031	GLS Parcel Business	DK	1,00	3,67	YPDIVN7L
30-05-2020	1000000031	GLS Parcel Business	DK	1,00	3,67	YPDIVN7M
01-06-2020	1000000031	GLS Parcel Business	DK	1,00	3,67	YPDIVN7N
02-06-2020	1000000031	GLS Parcel Business	DK	1,00	3,67	YPDIVN7O
02-06-2020	1000000031	GLS Parcel Business	DK	1,00	3,67	YPDIVN7P

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

SubCarrierName	SubCarrierId	AgreemtnStartDate	AgreemtnEndDate	ProductId	CarrierProductText	UBCarrierProductText	MinWeight	MaxWeight	PickupCountry	DeliveryCountry
GLS Denmark	1000000008	01-01-2020	31-12-2020	1000000031	BusinessParcel	GLS Parcel Business	0	1		DK
GLS Denmark	1000000008	01-01-2020	31-12-2020	1000000031	BusinessParcel	GLS Parcel Business	1	5		DK
GLS Denmark	1000000008	01-01-2020	31-12-2020	1000000031	BusinessParcel	GLS Parcel Business	5	10		DK
GLS Denmark	1000000008	01-01-2020	31-12-2020	1000000031	BusinessParcel	GLS Parcel Business	10	15		DK
GLS Denmark	1000000008	01-01-2020	31-12-2020	1000000031	BusinessParcel	GLS Parcel Business	15	20		DK
GLS Denmark	1000000008	01-01-2020	31-12-2020	1000000031	BusinessParcel	GLS Parcel Business	20	25		DK
GLS Denmark	1000000008	01-01-2020	31-12-2020	1000000031	BusinessParcel	GLS Parcel Business	25	30		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?

[Qiu · Accepted solution]

@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.

[AkimasaKajitani]

After the Join tool, You should connect the Filter tool.
It will work.