Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Fuzzy Match - Key Match only - Not working as expected

Kuehner
7 - Meteor

Hi there,

 

I am fuzzy matching two data sources with city and country information. Source one having clean information and source two four columns with a lot of other garbage in it.

In a first step I indentified the country information out of four different columns of source two.

In a second step I want to fuzzy match the city information out of the very same four columns from source two but leverage the country information of the first step.

Therefore I setup the fuzzy match tool to conduct firstly a "Key Match Only" (Generate Keys: "Whole Filed (Case Insensitive)" and Match Function: "None - Key Match Only")

and secondly a fuzzy match on the city name.

 

My expectation was that fuzzy matches on the city name are only conducted if the country name is perfectly the same.

To my surprise this was only true for most of the cases but not ALL the cases.

How is that possible?

 

Cheers Tom

 

P.S. I tried to re create this in a simple example flow, but there everything works as expected :(

 

9 REPLIES 9
hilton
7 - Meteor

Hi Tom,

 

I think your confusion stems from the wording of the ZIP code type fuzzy matching included in the Alteryx sample workflows and demos. In those fuzzy examples, the ZIP code is used in "key match only" mode, and it is explicitly stated that it will math the whole ZIP exactly, then go on to the other fields and fuzzy.

 

The difference here is NOT because you're using for country the key match type of "Whole Field (Case Insensitive)" while ZIP code is using "Digits Only". The difference is in the maximum key length. If yours is left as default, then it will be 3 or 4 characters long. In some cases, as in the screenshot I've attached below, two countries may share the same first 3 or 4 phonetic key chars. Such examples of matching pairs are Austria and Australia, having a shared 3-char key of "AUS", as well as South Korea and Dem. Pppls. Rep of Korea , both of which share the word Korea, which would have the same key (If you check the option of Generate Keys for Each Word).

 

The "Whole Field" match type is misleadingly named. It kinda means that it will create keys from any characters in that field, be they letters, digits, special characters etc, but it won't necessarily use the entire length of the field. To do that, you must increase the "Maximum Key Length" to 10 or so. You might also want to uncheck the "Generate Keys for Each Word" option, only in this particular case.

  

Fuzzy example of Countries Cities and ZIP

 

Hope that is clear enough! Let me know if that solves it.

Kuehner
7 - Meteor

Hi hilton,

 

thank you for your reply!

Actually I wasn't aware of the ZIP code sample ;)

 

Since I selected the key match type "Whole Field (Case Insensitive)" my expectation was, that the key resamples the letters of the country name (which seems to be true in my simple test flow I made) therefore your suggested issue (having the same phonetic key chars) should not be the case. (Just for information: I am using the 2 digit country ISO codes not the full name).

 

Any other ideas?

 

Cheers Tom

 

Capture.PNG

 

"Applicant Line" being the source for a potential city informatin, "City Names" is a clean list of citiy names and the Country ISO codes from the two sources ....

 

Capture.PNG

Capture.PNG

 

hilton
7 - Meteor

Can you open the "Edit..." options dialog for your Country ISO Code field, and take a screenshot? Or otherwise, attach your workflow to this discussion if you can.

 

I suspect the key to answering this question lies in those settings.

Kuehner
7 - Meteor

Sure, I can insert the screenshotl I can post the flow as well, but only without data, which would than probably not that helpfull anymore  ...

 

Capture.PNG

Kuehner
7 - Meteor

Acctually I found the issue: If the ID of source A and source B is both just numeric Alteryx mixes them up and it is impossible to extract them vom the "ID" and "ID2" which will be outputted by the Fuzzy Match. Now I understand why in the templates / examples they combine the ID number with the source name ;)

hilton
7 - Meteor

Ah, that would explain it. I never considered the ID fields, which have to have universally unique values. Thanks for closing this one out.

Kuehner
7 - Meteor

If you leave the country match away it works perfectly without universally unique values ...

 

Another interesting observation: If you define the ID from one stream between 1 and 1000 and for the other stream between 10000 and 11000 (being universally unique) the fuzzy match mixes them between the newly generated ID and ID2 fields. If you add the source like A1 to A1000 and B10000 to B11000 this does not happen ;)

hilton
7 - Meteor

That is interesting to hear, to say the least! I would not have expected it to behave that way.

 

In other words, what you're saying is that Alteryx doesn't maintain the integrity of the ID keys that are in the format of numbers only, in that it switches them around between ID and ID2? Thats not good news.

 

When you do that, with numbers only (1 to 1000, and 10000 to 11000), is your ID field a numerical type field, eg. int32, double etc? Or is it a String type? I always make sure to use String ID fields, which is maybe why I've never seen this happen before. Maybe Alteryx cannot handle ID fields in numerical types properly?

Kuehner
7 - Meteor

I configured the ID as string.

But since you raised that point I tried it with int32, and once of a sudden the ID's don't get mixed up anymore!

 

-> Using an ID consisting only out of numerics and setting it to string is obviously not a thing one should do!

 

Labels