Join on non-exact matches

Question

Hello,

I am trying to do a join where the fields won't necessarily match. Here is a simplified version of what I am trying to accomplish:

File 1:

GroupNameScript CountAtrium Centers, Inc14

File 2:

GroupnameTotal Script CountAtrium Centers Inc87

Combined:

GroupNameScript CountTotal Script CountAtrium Centers Inc1487

I tried using Fuzzy Tool and I wasn't getting the results I was looking for. I kept getting a lot of duplicates that looked like this:

GroupNameScript CountTotal Script CountAtrium Centers Inc1487Atrium Centers Inc1487Atrium Centers Inc1487

Any advice would be appreciated!

NicholasM · Answer

Hey @tycarbone,

A couple things I want to point out. The fuzzy match tool, when using the Merge match style, will attempt to match ever record from 1 data set to every record of the other. This will lead to something like Atrium Centers Inc from file A to be matched to Atrium Centers, Inc from File B, and vise versa (File B matching to File A). It is best practice to follow up the Fuzzy Match tool with a Unique Tool, to remove duplicates.

If you want some more assistance with the fuzzy match tool take a look at this live training led by one of my colleges Nick Smith.

I will also point out that it is best practice to proceed the fuzzy match tool with a Data Cleansing to standardize case and remove any unwanted characters. Sometimes this resolve all differences between two files and you could simply use a Join.

Let me know if you have Questions.

dougperez · Answer

Have you tried the find replace?