Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

IF Statement for beginner

Tommaso83
Alteryx Alumni (Retired)

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 😉

13 REPLIES 13
jacob_kahn
12 - Quasar

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
16 - Nebula
16 - Nebula

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!

Tommaso83
Alteryx Alumni (Retired)

Thank you so much

 

For Formula 2, what is the expression if I have multiple scenario such as

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

IF [Company Name] = "DEF" THEN [First Name] +"."+ [Last Name]+"@def.co.uk" ?

 

Again, I'm a beginner here 😉

 

Thanks

jacob_kahn
12 - Quasar

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

ENDIF

 

Let me know if you have a problem with that!

Tommaso83
Alteryx Alumni (Retired)

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
12 - Quasar

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 🙂

 

 

Tommaso83
Alteryx Alumni (Retired)

Something else has occurred

 

What is the expression for multiple trimright?

 

TrimRight([Name],"msc") +

TrimRight([Name],"bsc").....

jacob_kahn
12 - Quasar

Not sure I understand your question - I apologize.

 

Can you clarify a little?

 

 

Tommaso83
Alteryx Alumni (Retired)

Sure

 

Is some scenarios I want to remove Msc, in others Bsc and so forth so I'd like to know the expression for using trimright with more than 1 word. 

Hope it's clear 😉

Labels