How to implement this Excel formula within Alteryx?

[tinonsaha]

Hi all,

I want to use this expression I have in Alteryx using the formula tool, but I run into an error when I paste it in. I've changed all of the match functions to Contains. Any advice on how to get Alteryx to be able to read this?

IF (MATCHES ([client_name], "a"), "b", IF (MATCHES ([client_name], "*c*"), "b", IF (MATCHES ([client_name], "d"), "b", IF (MATCHES ([client_name], "e"), "b", IF (MATCHES ([client_name], "f"), "b", IF (MATCHES ([client_name], "*g*"), "b", IF (MATCHES ([client_name], "*FS MANILA*|*FS APAC*"), "b", IF (MATCHES ([client_name], "*h*"), "b"))))))))

[Claje · Accepted solution]

Hi,

The following formula should work:

IIF (CONTAINS ([client_name], "a"), "b", IIF (CONTAINS ([client_name], "*c*"), "b", IIF (CONTAINS ([client_name], "d"), "b", IIF (CONTAINS ([client_name], "e"), "b", IIF (CONTAINS ([client_name], "f"), "b", IIF (CONTAINS ([client_name], "*g*"), "b", IIF (CONTAINS ([client_name], "*FS MANILA*|*FS APAC*"), "b", IIF (CONTAINS ([client_name], "*h*"), "b","Other"))))))))

I made three changes to the formula so that it would work successfully.

I changed MATCHES to CONTAINS (as you had mentioned).

I changed IF to IIF.  IF in Alteryx has the specific syntax of using IF THEN ELSE ENDIF, where IIF uses parentheses and is similar/equivalent to the Excel IF statement in terms of syntax.

I added one final step ,"Other" at the end of the formula.  IIF and IF in Alteryx always expect an ELSE condition, so we had to have something here.  You can replace "Other" with anything you want, but you do need a condition here.

Note that I didn't spend a lot of time testing your individual search criteria, so I don't know if things like *g* will work exactly the same as they did in Excel, and *FS MANILA*|*FS APAC* has the same caveat.  Definitely test this to make sure it works how you expected.

[tinonsaha]

---

@Claje wrote:

Hi,

The following formula should work:

IIF (CONTAINS ([client_name], "a"), "b", IIF (CONTAINS ([client_name], "*c*"), "b", IIF (CONTAINS ([client_name], "d"), "b", IIF (CONTAINS ([client_name], "e"), "b", IIF (CONTAINS ([client_name], "f"), "b", IIF (CONTAINS ([client_name], "*g*"), "b", IIF (CONTAINS ([client_name], "*FS MANILA*|*FS APAC*"), "b", IIF (CONTAINS ([client_name], "*h*"), "b","Other"))))))))

I made three changes to the formula so that it would work successfully.

I changed MATCHES to CONTAINS (as you had mentioned).

I changed IF to IIF.  IF in Alteryx has the specific syntax of using IF THEN ELSE ENDIF, where IIF uses parentheses and is similar/equivalent to the Excel IF statement in terms of syntax.

I added one final step ,"Other" at the end of the formula.  IIF and IF in Alteryx always expect an ELSE condition, so we had to have something here.  You can replace "Other" with anything you want, but you do need a condition here.

Note that I didn't spend a lot of time testing your individual search criteria, so I don't know if things like *g* will work exactly the same as they did in Excel, and *FS MANILA*|*FS APAC* has the same caveat.  Definitely test this to make sure it works how you expected.

---

This ended up working, thank you so much!

[ivoller]

I'm not quite sure what you're trying to achieve here since it seems that the result will always be "b". However, using the IF c THEN t ELSE f ENDIF should work.

Something like

IF Contains([client_name],"a") THEN "b" ELSIF Contains([client_name],"*c*") THEN "b" ELSEIF ... ENDIF

Can you share sample data and what you need to achieve?

[jdunkerley79 · Accepted solution]

I'd suggest thinking about using a REGEX Match for this expression:

IIF(REGEX_MATCH([client_name],".*([adef]|(\*[cgh]\*)|(\*FS MANILA\*)|(\*FS APAC\*)).*"),"b","Other")

or similar should do the same as you had.