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: VINMeasurement_DateTimeMeasurement_ValueAABBCCDDEEFFGG2023-07-17 14:19:554.79788AABBCCDDEEFFGG2023-07-17 14:19:564.89238..............HHIIJJKKLLMMNNOO2023-07-19 14:25:450.408938HHIIJJKKLLMMNNOO2023-07-19 14:25:460.4075............ The second table is a set of records of vehicles passing through different assembly line stations, relevant fields below: VINStation_Start_DateTimeStation NameAABBCCDDEEFFGG2023-07-17 14:19:01Station AAABBCCDDEEFFGG2023-07-17 14:29:35Station BAABBCCDDEEFFGG2023-07-17 14:39:15Station CHHIIJJKKLLMMNNOO2023-07-19 14:16:15Station AHHIIJJKKLLMMNNOO2023-07-19 14:23:36Station BHHIIJJKKLLMMNNOO2023-07-19 14:28:32Station 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 VINMeasurement_DateTimeMeasurement_ValueStation_NameAABBCCDDEEFFGG2023-07-17 14:19:554.79788Station AAABBCCDDEEFFGG2023-07-17 14:19:564.89238Station A.............. HHIIJJKKLLMMNNOO2023-07-19 14:25:450.408938Station BHHIIJJKKLLMMNNOO2023-07-19 14:25:460.4075Station 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

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

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.

Screenshot 2023-08-03 105226.png

Thank You!

Javier

Join

Accepted answers

Christina_H

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

Time matching.yxzp

All comments

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?

Time matching.yxmd

Christina_H

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

Time matching.yxzp

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!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335