Tolerant match with DateTimeDiff when comparing records from two distinct sources

[emmanuel_varesio]

Dear All,

I would like to join records from two distinct sources based on a DateTime field tolerant match, e.g. if the difference between DateTime from source 1 and 2 is less than 15 seconds, join the records.

I could achieve this DateTime tolerant match with an "append fields" tool followed by a DateTimeDiff function (see attached workflow), but when the number of source 1 & 2 records grows, the resulting dataset size can grow dramatically after the Append tool.

Any help appreciated to find a more efficient way bypassing the "Append Tool".

Best regards,

Emmanuel

[binuacs]

@emmanuel_varesio Are you comparing all the fields to the source 2 or one to one mapping? I mean first record from the first file with the first record from the second file?

[emmanuel_varesio]

@binuacs  I'm comparing all the fields from source 1 to source 2 and in case of a tolerant match I would like to join the two records.

thanks.

Emmanuel

[Qiu]

@emmanuel_varesio 
It is quite interesting question 😁
Append can be very exhausing as you mentioned, and I think Join is not so.
So maybe we can think like this

1. For those date not near midnight, we can join them by Date first, which should significantly reduce the expansion of data stream.

2. For those date near midnight, which is believed to be less, we then use an Append too. (we can also do a Date Join by Minus or Plus one but should change much.)

[aatalai]

@emmanuel_varesio take a look at this 

[Qiu]

@aatalai 
Very nice thinking. 😁
Just wondering what happens if the Input 2 file contains large amount of records😂 

[aatalai]

@Qiu I agree I would suggest transposing but think @emmanuel_varesio wants to keep the number of records down, so that that if statement (in the final formula tool) might end up be very long 😅

[Qiu]

@aatalai 
Right, that is why I think we should transpose anyway to avoid writting formulas in the last formula tool. 😁

[aatalai]

@Qiu agree, just up to @emmanuel_varesio  top decide if they want a long formula or many rows of data, as otherwise the append tool originally done could be  a solution

[binuacs · Accepted solution]

@emmanuel_varesio Another method using batch macro