Matching column data to add a new column for entity name

[calvinwong]

Hi,

If I have the following data,

Column1

"A2"

"ADS3"

"AFE9_2"

"BAD9_3" 

and I'm trying to use a formula tool to add another column to match them to the entity code. I tried to use iff(contain([column 1], "2", 'XYZ', iff(contain([column 1], "3", "ABC", iff(contain([column 1], "9_2", "ERF", "DFS"))). However, given 9_2 also contain a 2, the formula doesn't work. Is there another way to write a formula so that it can return the correct entity name?

[jasperlch]

Hi @calvinwong

There are a couple of things you need to update in your formula:

1. update your iff to iif function

2. update your contain to contains function

3. you need to add an ending bracket for the contains function

4. for the conditional statement,  you could simply arrange your nested if conditions. The rule of thumb is always put the more restricted conditional statement (i.e. the condition that is more difficult to matched, e.g '9_2' is more difficult to match than '2') first.

Below should work:

iif(contains([column 1], "9_2"), "ERF", iif(contains([column 1], "2"), 'XYZ', iif(contains([column 1], "3"), "ABC",  "DFS")))

[MarqueeCrew · Accepted solution]

Regex_Replace([field1],"(\u+)(\d.*)",'$1|$2')

That will get you something easy to parse. You'll have an easy time joining to your search terms.

Cheers,

Mark

[calvinwong]

Thanks Mark. That was helpful. Much easier to parse.

I'm trying to learn more and understand about the regex expressions. Is there a good site or place that you recommend?

Thanks,

Calvin 

[MarqueeCrew]

You'll find KB articles here and if you check YouTube "RegEx Marqueecrew Alteryx" you'll find more of my content.

I like regex101.com as an outside resource. Introducing Regular Expressions by Michael Fitzgerald is a textbook resource that I've purchased.

Cheers,

Mark