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
Solved! Go to Solution.
Hi @delcarpiosamuel \
The attached workflow and contained formula should help you achieve this:
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
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
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
Thank you so much!! It works perfectly