community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Find and replace with multiple criteria and fuzzy match cities help!

Alteryx Partner

This is an abreviated version of the workflow that I am working on but should be sufficient for you to help me, if you can please - I feel that I am going round in circles more times than I care to think about!!

 

I have a list of cities which cannot be matched against the file (which I found on the internet) of cities and county they belong to ("Master file").

 

[This is an aside but it will help my learning] - Also, in the Master file it lists multiple zip codes in one column so I parsed these to separate columns to do a look up against them and they the output you see here is where there are still no matches (I am not sure if there is a quicker way of doing multiple find and replace fields as an aside?) - also I had to change the field type to string to get the lookup to work so is there way of searching and replacing numbers too?

 

 

alteryx screenshot.jpg

 

 

 

 

 

 

In any case, I've tried using Fuzzy Match to try and match the Sales data against the Master file and I just can't get it right, so I've stripped it back and hoping that someone can help fill in the blanks to help me conquer this fuzzy match trickery!  I want to ensure that the fuzzy match only kicks in when the States are the same, so I selected exact match for the state when I tried the fuzzy match - but let me know if I did something wrong there too!

 

Also, I don't have the CASS module available so I can't use that unfortunately (I did get mildly excited when I spotted it!).

 

Thanks

Alteryx Certified Partner

Hi @ck2019 can you attach the .csv files and your xls spreadsheet?

Alteryx Partner

@carlosteixeira2005 Whoops - sorry I meant to upload the package instead! Thanks for looking at this for me.

Alteryx Certified Partner

Hi @ck2019 

 

Take a look at this workflow and let me know if this help you...

 

Something like this?

 

Tks

Alteryx Partner

I think you reuploaded my version instead of yours!? Thanks for your time though, appreciate it...

Alteryx Certified Partner

Whoops, sorry.

 

try again

Bolide

Take a look at the example workflow under the Make Group tool

 

The example combines total sales amounts for vendors with similar names, using a fuzzy match

 

Alteryx Partner

Hey, I think the join here works wonders but there is a little bit more to do.  I also thought more about the multiple zips existing in the zip column and applied a text to column and then a transpose - it takes a little longer to run but deals with this nicely- I won't post it but 

 

I think the only thing that I need now is a fuzzy match on the left join as per the attached screenshot, I really want to have the City's populated with the correct spelling where the states/zip code are the same.  I'm sure it's achievable - I just can't figure it out...! Thank you

 

alteryx screenshot2.jpg

 

Alteryx Certified Partner

I will check

Bolide

To correct the City name.....Summarize by Zip and City, Sort by Zip then Count descending, then use the Sample tool to take the first record from each Zip.  In the Sample tool, group by the Zip.

Labels