IF Statement for beginner

Question

Hi Everyone

Completely new here.

Trying to build my first workflow and missing some basics

I would need help for 3 formulas:

1) FORMULA n.1: if mobile number starts with +, then replace + with 00

2) FORMULA n.2: if company is "ABC" then create email address firstname.lastname@abc.co.uk

3) FORMULA n.3: if last name contains added msc, then remove msc (example: if last name is Smith msc, then keep only Smith)

Thank you for your help 😉

jacob_kahn · Accepted Answer

1. IF StartsWith([Mobile Number],"+") THEN Replace([Mobile Number], "+","00") ELSE [Mobile Number] ENDIF

Make sure to replace [Mobile Number] with [Name of your field]

In Formula Tool - Create new field

2. IF [Company Name] = "ABC" THEN [First Name] +"."+ [Last Name]+"@abc.co.uk" ELSE "" ENDIF

3. TrimRight([Name],"msc")

Welcome to the Alteryx world! Let us know how we can help further.

J

Kenda · Accepted Answer

Hey @Tommaso83

Welcome to the Community and Alteryx!

Here are my recommended expressions to use in a Formula tool for each of your problems:

1.

iif(Substring([mobile number],0,1)="+",replace([mobile number],"+","00"),[mobile number])

- This uses an if statement that says if the first character of the mobile number field (string field) is a plus sign, replace that with "00", otherwise keep the mobile number as-is.

2.

iif([company]="ABC", [first name] + "." + [last name] + "@" + [company] + ".co.uk", null())

- This again uses an if statement to say if the company equals "ABC", then string together that email address, otherwise that field will be null.

3.

Replace([last name], " misc", "")

- I did not use an if statement for this one, but you certainly could. This expression simply looks for the string " misc" within the last name field and replaces it with the empty string.

Hope this helps!

jacob_kahn · Accepted Answer

Awesome question like this:

IF

[Company Name] = "ABC"

THEN

[First Name] +"."+ [Last Name]+"@abc.co.uk"

ELSE

IF

[Company Name] = "ABC"

THEN

[First Name] +"."+ [Last Name]+"@def.co.uk"

ELSE

""

ENDIF

Let me know if you have a problem with that!

Tommaso83 · Accepted Answer

One last question, sorry

if company is "ABC" and contact is John Smith what's the formula to create email address jsmith@abc.com?

Thanks again 😉

jacob_kahn · Accepted Answer

I would do something like this:

IF [Company] = "ABC" THEN Left([First Name], 1)+[Last Name] + "@abc.com" ELSE "" ENDIF

Let me know if there is anything else!

Appreciate the likes and acceptances 🙂

jacob_kahn · Accepted Answer

Sure @Tommaso83

Happy to assist a newbie to the Alteryx community!

So what I might do something in the attached workflow very quickly.

I'm sure you can do this in the formula tool and can try for you if you prefer

Thanks,

J

jacob_kahn · Accepted Answer

@Tommaso83

You can refer here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Replacing-multiple-values-in-one-string/td-p/69948 and here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-make-two-Replace-function-in-one-expression/td-p/841 for more information on replace multiple string values.

The RegEx solution just allows it to be dynamic, so that you don't actually have to specify all of the possibilities.

J

KilianL · Accepted Answer

2) FORMULA n.2: if company is "ABC" then create email address firstname.lastname@abc.co.uk

I guess you need a generic solution for this for any company name?

Use a formula tool to create the email like this: