Forum Discussion
Advocate VMultilple recursive Inner and Anti Joins for robust fuzzy date matching - Can List.Accumulate help?
Community ChampionI think a "simpler" group and sort would do the job here as well:
1) Group by "ID" and "Direction"
2) Sort by "Date" and add Index column
3) Merge on ID, Direction and new Index column in FullOuter-mode: Matches and non-matches will be shown
See attached file
JeffWeirHi ImkeF
Unfortunately that approach isn’t robust under all circumstances, as it will always match an entry in one table with a previous entry in the other table even if a closer match is available. And this is a very real possibility in this dataset.
Revised sample file and screenshot: Match to closest day_20190722 buffer Imke.xlsx
- ImkeF
- JeffWeir
My hard-coded query works fine, but I'm trying to find the time to see if I can learn how to write a dynamic function where I can specify the number of days to check either side. At the moment I progressively match the tables on larger values of a 'tolerance' factor X using this condition:
Table1.Date = Table2.Date +/- X .
And I'm effectiviely running this in a loop for values of X between 1 to 5, and removing successful matches on each pass leaving just unsuccessful ones. This lets me progressively increase the 'mismatch' tolerence on those dates, and remove matches at each pass leaving just the unmatched rows to do increasingly desperate matches on.
My query performs the following steps:
- Loads data from Table1 and Table2, and does an inner join on unique ID and Date
- Effectively removes these matches from Table1 and Table2 via an anti-join against each referenced table.
- Does an inner join on the remaining records, but this time offsets the dates in the Port table by +/- 1 day. This finds some more matches.
- Performs steps 2 and 3 over and over, increasing the date offset by 1 each time until I have captured all date mismatches up to a week.
The query dependency tree that results looks like so:
All that takes a lot of setup. I really need to work out if this can be turned into a function that is a) dynamic and b) efficient.
I still mean to take a crack at it, but it might be beyond my beginning/intermediate M.
For what it's worth, here's my current manual approach.
- Loads data from Table1 and Table2, and does an inner join on unique ID and Date
