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

Multi-Condition IF THEN ELSE statement

jlrouquette
7 - Meteor

There are many discussions about if statements but I'm struggling to find one in the format I'm looking for help with.

 

if [FIELD] < 6,000 THEN "A"

if [FIELD] is >= 6,000 and < 9,000 THEN "B"

if [FIELD" is >= 9,000 THEN "C"

 

I was able to manage 2 parts of the statement but I can't get the syntax right with the 3rd element without getting the "Malformed If Statement" warning. 

 

I appreciate any help!

 

-Luke 

17 REPLIES 17
Claje
14 - Magnetar

A really quick example of how you might write a formula like this:

if [FIELD] < 6000 THEN "A"

elseif [FIELD]  >= 6000 and [FIELD] < 9000 THEN "B"
else "C"
ENDIF

If you want to call out field "C" specifically, you might modify this as follows:

 

if [FIELD] < 6000 THEN "A"

elseif [FIELD]  >= 6000 and [FIELD] < 9000 THEN "B"
elseif [FIELD] >= 9000 THEN "C"
else "ERROR"
ENDIF

In this example, all values should be encapsulated into groups A, B, or C.  However, if something happens to be outside these categories, it would default to a value of "ERROR", which should make it really obvious downstream.  This has the advantage that if you accidentally wrote your "C" category as FIELD > 9000 (meaning values of exactly 9000 would have no category), they would flag as "ERROR", which would help you to identify a problem with the function.

CiaranA
10 - Fireball

Hi attached a solution.

Does this work for you ok?

jlrouquette
7 - Meteor

Both replies where prefect, thank you!  I actually had to convert it to 5 classifications so I used the first reply and added in a couple addition ELSEIF's. 

 

Thank you both for your help!

 

-Luke

Claje
14 - Magnetar

Hi,


Just wanted to point out something really quick in the attached workflow:

The second condition (ELSEIF Field2 >= 6000 <9000) will always evaluate to "TRUE" as I understand how Alteryx performs calculations, as the engine does not handle sequential evaluations in this manner.

 

As an example formula, the following :

IF 5000 >= 6000 <=1 THEN 'Evaluated to True'
ELSE 'Evaluated to False'
ENDIF

 

 

Will always return "Evaluated to True", even though 5000 is not greater than 6000, and neither 5000 or 6000 is less than or equal to 1.

 

What happens when this formula is evaluated is the first operators are compared:
5000>= 6000
This runs through the Alteryx engine, and is calculated and evaluates to FALSE - which is a binary value that can also be written as 0.

Then, the engine takes the result of this calculation - FALSE/0, and compares it to the next operator

0 <= 1

This evaluates as TRUE, as 0 is <= 1

 

The same thing happens with the Field2>=6000<9000 example, broken out in two cases below:

Value of 7000:

 

7000>= 6000
This runs through the Alteryx engine, and is calculated and evaluates to TRUE - which is a binary value that can also be written as 1.

Then, the engine takes the result of this calculation - TRUE/1, and compares it to the next operator

1 < 9000

This evaluates as TRUE, as 1 is < 9000


Value of 9500:

9500>= 6000
This runs through the Alteryx engine, and is calculated and evaluates to FALSE - which is a binary value that can also be written as 0.

Then, the engine takes the result of this calculation - FALSE/0, and compares it to the next operator

0 < 9000

This evaluates as TRUE, as 0 is < 9000

 

In general, to accomplish what you are looking for, you will need to use the AND operator, and restate the initial value, eg:

[Field2] >= 6000 AND [Field2] < 9000.

 

There's an Idea (That I posted, shameless plug) to add a BETWEEN operator which might enable functionality closer to the proposed formula that @CiaranA had suggested.  You can see it here, and add stars if you think this would be valuable.

 

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Add-quot-BETWEEN-quot-as-an-operator-functio...

CiaranA
10 - Fireball

Sorry I did update my workflow to work correctly, but yes a between so you didn't have to write the logic part twice would be nice.

 

 

jlrouquette
7 - Meteor

Just to clarify, are you saying that managing it the way I've listed below would work correctly or could still face the issues you noted?

 

IF [FIELD] < 6000 THEN "Very Short"
ELSEIF [FIELD] >= 6000 and [Horizontal Length] < 7100 THEN "Short"
ELSEIF [FIELD] >= 7100 and [Horizontal Length] < 8300 THEN "Mid"
ELSEIF [FIELD] >= 8300 and [Horizontal Length] < 10050 THEN "Long"
ELSEIF [FIELD] > 10050 THEN "Very Longl"
ELSE "ERROR" ENDIF

CiaranA
10 - Fireball

That should work fine yep, you can reference other fields as well with your AND as shown in the second formula tool in attached example.  

Claje
14 - Magnetar

Just chiming in to say that this is exactly right!

gotu
6 - Meteoroid

IF [Alternative BOM Text]= "SHIP & ENTER SUB"
THEN "SES"
ELSEIF "SUB ONLY" THEN "SON"
ELSEIF "SHIP ONLY" THEN "SHO"
ELSE "ERROR"
ENDIF

 

What is the error in this formula??

 It's not giving the correct output.. Ony First condition is met and I'm not getting the Second and third one

 

Can some one please help

Labels