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

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.