Tolerant match with DateTimeDiff when comparing records from two distinct sources

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

Example.yxmd

Date Time

Join

Accepted answers

binu_acs

@emmanuel_varesio Another method using batch macro

Example (10)_updated.yxzp

All comments

binu_acs

@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

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

Example-r1.yxmd

aatalai

@emmanuel_varesio take a look at this

Example.yxmd

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

binu_acs

@emmanuel_varesio Another method using batch macro

Example (10)_updated.yxzp

emmanuel_varesio

Dear All,

First of all I apologize for my long silence, I was out of town and busy on other projects. Although I like your three ways of solving my issue (and I thnak you all for your efforts), I will vote for the macro solution that I find quite tidy and versatile in my global workflow.

I really appreciate your help that could solve my problem.

Take good care (and once again sorry for my long silence).

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703