Fuzzy Match

Question

I am using fuzzy matching for addresses. I created an 'address value' that is a combo of address line 1 and 2, formatted like 19 Yellow rd,2.

The issue is that if I have a value like 19 yellow rd, it does not match with 19 yellow rd,2, and I would like instances like this to match. However inversely, if it does have a ste # like 19 yellowrd,2 I do not want it to match with 19 yellow rd. Any tips on how to configure this?

CailinS · Answer

Ok this got hairy pretty quickly but I do think knowing if you have a 'master' table where you're joining *to* versus comparing all records like I have done would help clean up some of the logic. Long story short - the idea is to match using Suite in one match, that is your 'best' matches so you want those first. Then stacking on any matches that come without considering suite at all. Then filtering out any time the 'left record' which I'm hoping ends up being your 'master' table is NOT NULL (meaning there is a suite but it was ignored per my config). I hope the example helps to see some options you could try to apply and it will boil down to multiple matches, joins, and filters instead of trying to accomplish your logic inside of one fuzzy match tool which isn't possible in a way I can imagine.

Fuzzy Match Example with suite and with no suite.yxmd

CailinS · Answer

@pbresney I have an idea that actually has them separate (which allows you to set separate rules/logic/match thresholds so I'm going to try to build out a sample workflow to show what I'm thinking. Be back soon 😀. If you see this...One question that may help or change my solution is: Are all the addresses coming from the same file? or do you have 2 files like 'primary table' and 'new table' or some other delineation between the records you're trying to match?

And to be extra clear, I think you're saying "if there IS a suite number insist on the suite number matching the other record" otherwise, "if there is no suite number, allow it to match with records that have a suite number". If this is accurate, it makes me think you have a sort of 'master table' that you're trying to match to and you want to err on the side of 'more data' when you have it and then if you dont, you're willing to assume its a match. One question then would arise...what if there were two records on the 'net new side' that had say 19 Yellow Rd, 2 and 19 Yellow Rd, 3 trying to match against 19 Yellow Rd in the master table - I can't think of a way to know which to match to so it would match to both....is that ok? something you'll have to think through if its a possibility.