Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Cross file validation with date range

Hamder83
11 - Bolide

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_AWB
29-05-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7L
30-05-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7M
01-06-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7N
02-06-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7O
02-06-20201000000031GLS Parcel BusinessDK1,003,67YPDIVN7P


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

SubCarrierNameSubCarrierIdAgreemtnStartDateAgreemtnEndDateProductIdCarrierProductTextUBCarrierProductTextMinWeightMaxWeightPickupCountryDeliveryCountry
GLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business01 DK
GLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business15 DK
GLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business510 DK
GLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business1015 DK
GLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business1520 DK
GLS Denmark100000000801-01-202031-12-20201000000031BusinessParcelGLS Parcel Business2025 DK
GLS 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?

2 REPLIES 2
Qiu
20 - Arcturus
20 - Arcturus

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

AkimasaKajitani
17 - Castor
17 - Castor

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

 

AkimasaKajitani_1-1603803399711.png

 

AkimasaKajitani_0-1603803295100.png

 

 

 

Labels