Multiple IF Statements (IFS)

Question

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.

Thank you!

Sam

Screenshot.PNG

DavidThorpe · Accepted Answer

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

DavidThorpe · Accepted Answer

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