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

Creating a new flag based on a fuzzy match

jb
7 - Meteor

We have survey response data where people type in what brands come to mind when they think of travel. I am tracking 6 specific brands and need to create a separate column to flag out whenever someone responds with one of these brands.

 

My columns need to look like: Answer Value, Hotwire Flag, Expedia Flag, Priceline Flag, Booking.com Flag, Hotels.com Flag, Expedia Flag

 

Since the answer values are user submitted, there are all kinds of spelling variations (princeline, Price Line, Princessline, etc. for Priceline). How can I do a fuzzy filtering of each of these options to create the separate flag columns by brand?

 

I'm trying to do the equivalent of this type of sql query:

case when answer_value like %hot%wire% then 1 else 0 end as hotwire_flag

 

Thank you!

7 REPLIES 7
RodL
Alteryx Alumni (Retired)

You have at least a couple of options here...

You can build your own algorithm using RegEx for pattern matching. This gives you precise control on how you match up to the survey data and can be quite powerful.

You can use the Fuzzy Match tool. Here you can get a relative score to how close matches are, but you live with the existing algorithms in the tool.

 

Since you have a limited number of brands, I would probably start with the RegEx method myself.

I have attached a couple of workflows that will provide you with examples of both and added some basic explanations for the tools within the macros.

MarqueeCrew
20 - Arcturus
20 - Arcturus
Also you could try soundex()
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
RodL
Alteryx Alumni (Retired)

Good point @MarqueeCrew, I always forget about Soundex() being an available function. 

So there's a 3rd option...although it does have it's limitations...it is much simpler to work through.

 

I have attached an amended workflow that shows an example of that as well.

Thanks!

jb
7 - Meteor

Thanks @RodL and @MarqueeCrew. Big help!

jb
7 - Meteor

How can I make the regex statement more flexible so that it factors in spaces and dots? I attempted to run it with or statements (below) but i must be writing it wrong because pasted here are the results i'm getting.

 

Statement: h[a-z]*t([a-z]*|/s+)w[a-z]*r([a-z]*| */d[a-z]*)

 

Thanks in advance!

 Scenario 1.PNG

RobertBl
Alteryx Alumni (Retired)

I'm going to limit my answer to this particular question (and not the whole thread)

 

The following regex matches all your tests

 

h[a-z]*t\s{0,1}w[a-z]*r[a-z\s\.]*

 

h

[a-z]*            zero or more letters

t

\s{0,1}           zero or one space

w

[a-z]*            zero or more letters

r

[a-z\s\.]*        zero or more letters, spaces or dots

 

That covers your examples but it also allows for other items which you may or may not want. Definetely want to test it again all the values you've received.

jb
7 - Meteor

@RobertBl that did the trick! Thank you!

Labels