Join by two fields, first by exact string match, then DateTime approximate match

[javiroca]

Hi! My first post here, I have been searching the forums but no other question seems to match my problem

I have 2 tables (I will simplify for the question purposes)

The first one is a set of measurements taken once per second on different vehicles while on the assembly line:

VIN	Measurement_DateTime	Measurement_Value
AABBCCDDEEFFGG	2023-07-17 14:19:55	4.79788
AABBCCDDEEFFGG	2023-07-17 14:19:56	4.89238
.....	.....	....
HHIIJJKKLLMMNNOO	2023-07-19 14:25:45	0.408938
HHIIJJKKLLMMNNOO	2023-07-19 14:25:46	0.4075
....	....	....

The second table is a set of records of vehicles passing through different assembly line stations, relevant fields below:

VIN	Station_Start_DateTime	Station Name
AABBCCDDEEFFGG	2023-07-17 14:19:01	Station A
AABBCCDDEEFFGG	2023-07-17 14:29:35	Station B
AABBCCDDEEFFGG	2023-07-17 14:39:15	Station C
HHIIJJKKLLMMNNOO	2023-07-19 14:16:15	Station A
HHIIJJKKLLMMNNOO	2023-07-19 14:23:36	Station B
HHIIJJKKLLMMNNOO	2023-07-19 14:28:32	Station C

Desired Output: For every measurement taken, add a field with the station name where the vehicle was at the DateTime when the measurement was taken. We can assume that each station starts on Station_Start_DateTime and ends just before the next station

VIN	Measurement_DateTime	Measurement_Value	Station_Name
AABBCCDDEEFFGG	2023-07-17 14:19:55	4.79788	Station A
AABBCCDDEEFFGG	2023-07-17 14:19:56	4.89238	Station A
.....	.....	....
HHIIJJKKLLMMNNOO	2023-07-19 14:25:45	0.408938	Station B
HHIIJJKKLLMMNNOO	2023-07-19 14:25:46	0.4075	Station B
....	....	....

This is how I am doing it now, but it is extremely inefficient, as the Join block generates GB's of data when combining both tables. My dataset will grow over time, I just measured 2 vehicles and the PC struggles. I need to find a better solution as I need to measure tens of vehicles.

Thank You!

Javier

[javiroca]

Forgot to mention that there could be multiple records for each station in the second input table, as each process within the station generates a record. Thanks

[Christina_H]

How about something like this that generates a row for every second at that station?

[Christina_H · Accepted solution]

Or alternatively, construct a formula from the second input to calculate which station is correct

[javiroca]

Thank you, Christina! It seems to work. Just for my education, I don't understand what the marcro does, is there a way to see the code inside the macro? 

While importing you workflow I got a few errors due to my Alteryx version being older than yours, not sure if that has anything to do.

Thanks again,

Javier

[Christina_H]

Hi @javiroca, if the workflow imported correctly you should be able to right click on the macro to open it (option at the bottom of the menu).  But it's a very simple one - just replacing a fixed station value with the calculated formula instead.

As for the versions, I don't think I've used anything specific to the latest versions of Alteryx so hopefully after warning you it will all work correctly!