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/Replace vs. Fuzzy Matching: How to Replace a field with Match Keys

Highlighted
Atom
 

Hello,

 

My team is working on a dirty data set and we are very new to Alteryx.

 

We are trying to sort out and simplify a very messy "Work Order Description" field and gain some insight from it.

 

We've done fuzzy matching to find key words (one word) for matches within the field. We want to replace the WOD (Work Order Description) field with its corresponding matching keyword.

 

We keep getting stuck because the Find/Replace is not replacing the WOD field with the new key words.

 

Has anybody here undergone a similar project? Could you explain how you wrangled the data? What are the steps after fuzzy matching?

Is there a better way we don't know about to do this? 

 

Attached is a screenshot of our workflow so far, if you have any advice or insight please share!

 

Thanks,

Caroline439-RM-ALTERYX SNAP (1).PNG

 

 

Alteryx Certified Partner
Alteryx Certified Partner

@csmolski,

 

If you are looking to take a description field and match the individual words within it to another set of data and you get stuck because the FIND/Replace is not replacing the WOD with the new key words, then you might be asking too much too quickly.

 

Using your eyes, you see a WOD of:  Replace broken parts.  If the Keyword dictionary has Replace = Remedy and Broken = Defective, then you want to see:  Remedy Defective parts.

 

Did you see how I did that?

  1. I broke the description up into 3 words
  2. I looked each word up in the dictionary
  3. If I found the word in the dictionary, I exchanged the result with the lookup word otherwise I kept the original word
  4. I reconstructed the WOD

Using Alteryx, I would Tokenize the WOD and then I would use the find replace tool to lookup and exchange the values, finally I would reconstruct the WOD.

 

Here's how I did this in Alteryx:

 

Capture.PNG

 

I see that you are not using version 11.  I will post both a version 11 and a version 10.x for you.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Bolide

With a little tinkering on the great solution provided by @MarqueeCrew you can also search for partial words.  The issue can be inconsistency in spelling or tense.  I've run into these cases before where there is free form typing in certain fields.  By adding a column that has a partial spelling of the word, you can find words that don't exactly match.  That's probably why you were going down the fuzzy match path.  I added 1 more record to the example data that contains different versions of broken and replace.  It may not get you 100% there on every exact spelling, but it is a start assuming this is the issue you are dealing with.

replace5.JPG

  I've added a column named replace that has the partial word.  

 

replace.JPG

I then changed the Find/Replace configuration to find value in the new column, uncheck match whole word, and then I appended the keyword column.

replace2.JPG

Added a formula before the summarize tool to merge the correct words in with the original text that did not need to be replaced.

replace3.JPG

This is the output.

replace4.JPG

Atom

Hi guys,

 

Thanks so much for your advice and solutions! We've taken a look, but we don't think this will work for our particular problem.

 

Our WOD is extremely dirty, some entries have 10 characters, some have 200.

 

We have extracted keywords using fuzzy matching, now we want to replace the entire field with the single keyword.

 

For ex:

 

We have a WOD showing:

Small W/R toilet has air leak at regulator and ejector when flushing.  Causing to reset every flush.
Unit #4 ..Removed toilet and schroud found regulator leaking..Replaced regulator then the toilet will work only when the reset button is tripped..No replacement parts to be had..

 

And another showing:

large washroom toilet appears to have something stuck in the knife valve.

 

Our fuzzy matching has taken "Toilet" as our keyword, we want Find and Replace to take that keyword and replace it in the field.

 

Is something wrong in our process? Any suggestions on how to make it work? 

Bolide

I used your text and added 2 other work orders to demonstrate.  Not much different than the solutions provided.  Can skip the last join if you don't need the full text at all.  Also note record 3 in that a work order may have more than 1 keyword.  Can be handled differently, I chose to concatenate or you could have a different row for each keyword within a WO.

 

fuzzydescpritions.JPGfuzzydescpritions2.JPG

 

Labels