Look up dates

[xman1968]

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 1	Visits
Vehicle	Visit_date	Job
A	Jan 1, 2019	Brakes
A	Feb 1, 2019	Lights
B	Jan 15, 2019	Aib Bag
B	Feb 15, 2019	PMA
C	Jan 2, 2019	PAINT
D	Jan 2, 2019	Pre-delivery Inspection

Table 2	Contracts
Vehicle	from	to	customer
A	Dec 25, 2018	Jan 28, 2019	X
A	Jan 28, 2019	Feb 25, 2019	Y
B	Jan 12, 2019	Feb 14, 2019	Z
B	Feb 15, 2019	Jul 8, 2019	X
C	Jan 1, 2019	Jul 8, 2019	Y

Desired output
Vehicle	Visit_date	Job	customer
A	Jan 1, 2019	Brakes	X
A	Feb 1, 2019	Lights	Y
B	Jan 15, 2019	Aib Bag	Z
B	Feb 15, 2019	PMA	X
C	Jan 2, 2019	PAINT	Y
D	Jan 2, 2019	Pre-delivery	N/a

Is there a way to do this in Alteryx?  

Thanks

[BrandonB]

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 · Accepted solution]

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

[xman1968]

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.