If Then Else Statement with Multiple Condtions

[KamenRider]

Hi Guys,

I would like to ask for your ideas about how to set the correct formula for this conditions. If those conditions are meet, the Direct Route will become Thunder Route instead of Champ Route. I have tried using "AND" and "OR" but it doesn't give me the exact answer maybe because the formula tool get confuse. I've attached a test file for your reference.

I hope someone to help me with these.

Thanks so much.

Kamen

[DataNath]

Hey @KamenRider, is this what you're expecting as the output? I've annotated the expression for your own learning

IF [Document Type] = 'Hustler' AND --- Checks document type is Hustler
[Men Postion] IN ('1st Position', '2nd Position', '12') AND --- Checks [Men Postion] is either 1st Position, 2nd Position or 12
[Position Indicator] IN ('A','B','C','D') AND --- Checks that [Position Indicator] is A, B, C or D
IsNull([Point Release]) or IsEmpty([Point Release]) THEN 'Thunder Route' else 'Champ Route' ENDIF --- Checks whether Point Release is empty/null or not and if all conditions are met assigns 'Thunder Route'. If not, assigns 'Champ Route'

The IN function here is a quicker alternative to using Contains() multiple times.

[atcodedog05]

Hi @DataNath 

Great solution 😀 adding to your solution just a knowledge share IsEmpty() also check IsNull().  

https://help.alteryx.com/20221/designer/test-functions

Hence you can just use IsEmpty() like below

IF [Document Type] = 'Hustler' AND --- Checks document type is Hustler
[Men Postion] IN ('1st Position', '2nd Position', '12') AND --- Checks [Men Postion] is either 1st Position, 2nd Position or 12
[Position Indicator] IN ('A','B','C','D') AND --- Checks that [Position Indicator] is A, B, C or D
IsNull([Point Release]) THEN 'Thunder Route' else 'Champ Route' ENDIF --- Checks whether Point Release is empty/null or not and if all conditions are met assigns 'Thunder Route'. If not, assigns 'Champ Route'

Also in this scenario, you have to wrap the or condition is brackets (IsNull([Point Release]) or IsEmpty([Point Release])) to work properly

Hope this helps : )

[DataNath]

Thanks for pointing that out @atcodedog05 - always forget that IsEmpty() checks for both there!

Have also amended my expression for the correct spellings in [Men Postion] which were leading to the wrong outcome (I was using 1st Position/2nd Position instead of 1st Positon/2nd Positon):

Amended flow attached.

With regards to the bracketing, there was an interesting post recently by @fmvizcaino here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/AND-OR-precedence-rule/m-p/975968#M241...

[atcodedog05 · Accepted solution]

Hi @DataNath 

Thank you for sharing the post.

I guess there is no precedence in AND and OR. I have created different scenarios to test it out.

And as you can see when using without bracket row 10 even though Document Type is not Hustler but is Huggies it is been assigned to Thunder Route. This is because the last OR IsEmpty was True hence row 10 output was set to Thunder Route. But when we are using bracket or IsEmpty it is working properly as expected and row 10 is correctly set as Champ Route.

This is an interesting debate. If you have any other scenarios do share.

[KamenRider]

Hi @atcodedog05  and @DataNath 

Thank you so much for the solutions. I enjoy reading both of your thoughts. I've created a very long statements but you guys make it shorter and easier. This gives me more knowledge and will use these ideas to my other workflows.

Again, thank you so much.

Kamen

[atcodedog05]

Happy to help : ) @KamenRider 

Cheers and have a nice day!