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

Look up dates

xman1968
6 - Meteoroid

Hello,

 

I have a scenario where I need to find which lessee had a vehicle when it was serviced;

 

Basically, I need to look up the contract table and assign a customer number to the repair table.

 

Table 1Visits 
   
VehicleVisit_dateJob
AJan 1, 2019Brakes
AFeb 1, 2019Lights
BJan 15, 2019Aib Bag
BFeb 15, 2019 PMA
CJan 2, 2019PAINT
DJan 2, 2019Pre-delivery Inspection

 

Table 2Contracts  
    
Vehiclefromtocustomer
ADec 25, 2018Jan 28, 2019X
AJan 28, 2019Feb 25, 2019Y
BJan 12, 2019Feb 14, 2019Z
BFeb 15, 2019Jul 8, 2019X
CJan 1, 2019Jul 8, 2019Y

 

Desired output  
    
VehicleVisit_dateJobcustomer
AJan 1, 2019BrakesX
AFeb 1, 2019LightsY
BJan 15, 2019Aib BagZ
BFeb 15, 2019 PMAX
CJan 2, 2019PAINTY
DJan 2, 2019Pre-deliveryN/a

 

Is there a way to do this in Alteryx?  

 

Thanks

3 REPLIES 3
BrandonB
Alteryx
Alteryx

If each row in the first table corresponds to the sequential order of rows in the second column, you could use a record ID tool after both inputs, and then join on the record ID. Otherwise you need to establish some kind of primary key (field to join on). 

MichalM
Alteryx
Alteryx

@xman1968 

 

You will achieve this by generating a row for every day the lessee had the vehicle using the Generate Row tool. Once done, you will be then able to join the two data sets - see the attached.

 

lookup-dates.png

 

xman1968
6 - Meteoroid

Thank you!

 

This worked great. After generating the rows, I also created an ID by concatenating the vehicle and the date in the contract and the visits tables and joined them.

Amazing tools. Despite creating a large amount of data (since the contract table was massive and contracts span several years sometimes) it generated the output in a few minutes.

 

Thanks for the quick reply!

 

Regards.

Labels