Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Fuzzy match addresses from two different spreadsheets

Gabbz03
5 - Atom

Hi,

 

I'm super new to this but I am trying to match two different address columns from two different spreadsheets that will spit out the match and the percentage.

 

I have a primary address spreadsheet that contains over 60,000 addresses but also contains the floor, unit, and suite number at the end of the address:

 

Primary address table:

ID_Table Address
1123 Milner Ave Floor 1
2321 Argyle St Unit 2
3322 Elgin St Floor 3
4323 Elizabeth St Unit 3
5324 King St Floor 32
6325 Queen St Floor 4

 

I want to be able to match against my secondary table that contains approximately 2000 addresses. However, addresses in this table are messier and can contain the full address including postal code, city, punctuations, typos, etc. 

 

Secondary Address Table:

IDAddress
11000 Airport Rd, Ottawa, ON K1T 3E2
2Bronte Park, 1293 Burloak Drive (Park Office)
374 Wilson Rd
45 Steve Street
583 Lorne St.
63423 Bay Road. N (left)

 

I'm expecting this sort of output:

ID_Table 1Address_Table 1ID_Table 2Address_Table 2Percentage Match
1123 Milner Ave Floor 1No MatchNo Match%
2321 Argyle St Unit 24Ottawa, 321 Argyle St, K2L 3B2%
3322 Elgin St Floor 35322 Elgin St.%
4323 Elizabeth St Unit 32Bronte Park, 323 Elizabeth St (Park Office)%
5324 King St Floor 32No MatchNo Match%
6325 Queen St Floor 46325 Queen St. N (left)%
No MatchNo Match11000 Airport Rd, Ottawa, ON K1T 3E2%
No MatchNo Match374 Wilson Rd%

 

When I use the fuzzy match tool it doesn't seem to give me the results that I expect? Any ideas?

 

Thanks!

9 REPLIES 9
danilang
19 - Altair
19 - Altair

Hi @Gabbz03 

 

Can you update the data in your post. The data hat you're showing in Table 2 of your output table isn't in your input Table 2

 

Dan

Gabbz03
5 - Atom

Sorry, update made.

chashuett
6 - Meteoroid

was a solution to this issue regarding matching addresses from two separate tables answered? If so, kinldy post the result.

atcodedog05
22 - Nova
22 - Nova

Hi @chashuett 

 

This seems like an old post i dont think so there would be any response. Can you post a new question with sample data we can take a look into it. 

chashuett
6 - Meteoroid

thank you for you reply. please find attached two csv files, each containging one attrbute; namely, "Field1," and "Field2."

I'm looking for a way to determine how many and which addresses from file Field1 match those in Field2.

r/

Charles Huett 

atcodedog05
22 - Nova
22 - Nova

Hi @chashuett 

 

Here is how you can do it.

Workflow:

atcodedog05_0-1628358071248.png

 

1. Using record id tool to set row id.

2. Using formula tool to mark the source as file1 and file2. Using another formula to create custom ID for sources like F100001 (file1) and F200001 (file2). This will be useful in fuzzy matching.

3. Using union tool to join all the rows together.

4. Using fuzzy match tool merge mode. Merge mode match only among different sources this is why source label is required. And setting match id as source record id and match value is Field as address. This will match sources record ids wherever there is a value match.

5. Using join multiple tool to perform outer join and get the source2 ids (source 2 ids on which file 2 should be matched on)

6. Using join multiple tool to perform outer join on source2 id and join the data where value has fuzzy matched.

7. Using formula tool to flag as it matched.

5. Sorting to show matched at the top.

 

Hope this helps : )

 

chashuett
6 - Meteoroid

thank you very much!

the network is a thing of beauty...getting a little teary-eyed gazing at its spendor!!

 

However, I'm not able to download the yxmd as posted. 

 

please advise.

 

v/r,

Chas

atcodedog05
22 - Nova
22 - Nova

Hi @chashuett 

 

Try this. If it doesnt try refreshing the webpage and try again to download.

 

Hope this helps : )

chashuett
6 - Meteoroid

yes. that worked. got it.

v/r,

Chas

Labels
Top Solution Authors