Tuning the fuzzy matching tool

Hello the community,

I try to understand the logic and the functions of the various options for the fuzzy matching tool.

I'm currently having difficulties to use it on my workflow here attached.

What I try to do:

I have a table relatively messy, with countries and cities, and I would like to correct it based on the Cities database.

I would like first correct the countries (I might be able to correct both at once, but I know that some cities are in the country field, and vice versa, and that might not work as expected).

If a country cannot be identified in the Cities database, I rename it "Others".

But I see a lot of unmatched countries that should have been identified.

What could be changed to improved these results?

Thanks in advance for your help.

Cities.yxdb

Fuzzy_Logic_Workflow.yxmd

Main_DB.yxdb

Fuzzy Match

Accepted answers

JoshKushner

Try and fuzzy match with descending match percentages (100% -> 95% -> 90% -> 85% -> 80%) Then unite everything.

I've attached an updated version of your flow.

Flow:

Results:

result 1.PNG

result 2.PNG

Fuzzy Match.yxzp

All comments

JoshKushner

Try and fuzzy match with descending match percentages (100% -> 95% -> 90% -> 85% -> 80%) Then unite everything.

I've attached an updated version of your flow.

Flow:

Results:

result 1.PNG

result 2.PNG

Fuzzy Match.yxzp

FVarlet

Thank you Josh for your reply.

I'm analysing it by looking at what has been accepted and what has been discarded at each step.

And I observe curious behaviors.

First, I will have to check between each step if some cities and countries have been misplaced (a city in the country field and the corresponding country in the city field). It is for example the case at the 4th step (match at 85%) with the city of Macau (China) that is recognized as the Macao country.
In the same step, I see Austria-AT that is recognized as Austria in record #1, and as Australia in record #8.
Is there a way to prevent it ? I could remove any character after the "-" character, but that could prevent the detection of other countries.
Another effect I don't really understand: If I count the number of results after the first join tool, there are 106 records in the Left part, 167 records in the Join part, but only 218 records enter in this tool. Which means the fuzzy match tool produces duplicates. How can it be possible?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703