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

Multiple IF Statements (IFS)

delcarpiosamuel
6 - Meteoroid

Hi, 

 

I'm trying to use the formula tool to reproduce an If statement from excel but I can't seem to get it right. 

 

The excel formula is below. 

 

=IF(LEFT(D430854,1)="4",IF(LEFT(M430854,3)="614","PATCH","PM"),IF(C430854="JRZ","1"&MID(D430854,2,2),IF(C430854="SL","1"&MID(D430854,2,2)&"SL",D430854)))

 

Excel column D is Alteryx field "Prod Line"

Excel column M is Alteryx field "Name - Remark Explanation (GLEXR)"

Excel column C is Alteryx field "WO Start Site"

 

What I'm trying to accomplish is:

1) If column D starts with a "4" and if column M starts with "614" then I want the column to be say "Patch" for true and "PM" for false.

2) If column C is "JRZ" then I want the column to say "1" plus the middle characters of column D. (In the screenshot below, column C is "JRZ" and column D is "013" so I want column E to say "113")

3) If the column C is "SL" then I want the column to say "1" plus the middle characters of column D. 

 

delcarpiosamuel_1-1573831060854.png

 

Thank you!

Sam 

4 REPLIES 4
DavidThorpe
Alteryx
Alteryx

Hi @delcarpiosamuel \

 

The attached workflow and contained formula should help you achieve this:

 

Spoiler

IF [C] IN ('JRZ','SL')
THEN '1' + Substring([D],1,2)


ELSEIF left([D],1) = '4'
AND LEFT([M],3) = '614'
THEN 'Patch'


ELSE 'PM' ENDIF

This first tests the appearance of SL or JRZ in column C and prepends the character '1' to make 013 into 113.

It then tests if the left first character of column D is 4 AND column M begins with 614 and applies 'Patch' or 'PM' accordingly.

 

I may have missed something, or some other situations in your data so please let me know how you get on!

 

Thanks 

 

David 

delcarpiosamuel
6 - Meteoroid

Hi David, 

 

Thank you!!

It worked!

 

I realized however, that I wasn't clear in the last part of the formula. 

 

I want the formula to tell me if [C] is "SL" to give me '1'+ Substring([D],1,2) and when false to show [D]. 

Your current formula 100% shows when true but how can I add when false to just get [D] when [C] is "SL"?

 

Below is the excel formula and the blue font part is what I wasn't clear with in the first post. 

 

=IF(LEFT(D433196,1)="4",IF(LEFT(M433196,3)="614","PATCH","PM"),IF(C433196="JRZ","1"&MID(D433196,2,2),IF(C433196="SL","1"&MID(D433196,2,2)&"SL",D433196)))

 

Basically I want

1) if [D] starts with '4' then if left 3  [M] is '614' show "Patch" for true and "PM" for false when [D] doesnt start with '4' then 

2) if [C] is 'JRZ' then '1'+ last 2 characters of [D] when true and when [C] isn't 'JRZ' then 

3) if [C] is 'SL' then '1'+ last 2 characters of [D] when true and when [C] isn't 'SL' then

4) [D]

 

Is there a way to make a formula to show all of that?

 

Thank you

Sam 

DavidThorpe
Alteryx
Alteryx

Hi @delcarpiosamuel 

 

Great! I wasn't sure on your 'else' part so took a guess!

 

Try the following instead:

 

IF left([D],1) = '4'
AND LEFT([M],3) = '614'
THEN 'Patch'

 

ELSEIF left([D],1) = '4'
AND LEFT([M],3) != '614'
THEN 'PM'

 

ELSEIF [C] IN ('JRZ','SL')
THEN '1' + Substring([D],1,2)

 

ELSE [D] ENDIF

 

Thanks 

delcarpiosamuel
6 - Meteoroid

Thank you so much!! It works perfectly

Labels