Creating a new flag based on a fuzzy match

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!

Fuzzy Match

Preparation

Accepted answers

RodL

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.

RegEx example.yxmd

Fuzzy Match example.yxmd

RobertBl

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\.]*

[a-z]* zero or more letters

\s{0,1} zero or one space

[a-z]* zero or more letters

[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.

All comments

RodL

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.

RegEx example.yxmd

Fuzzy Match example.yxmd

MarqueeCrew

Also you could try soundex()

RodL

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!

RegEx example.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703