Multi-Condition IF THEN ELSE statement

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

Help

Accepted answers

Claje

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.

All comments

Claje

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

CiaranA

Hi attached a solution.

Does this work for you ok?

Alteryx Community - Example Solution.yxmd

jlrouquette

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

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-function/idi-p/79219

gotu

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

Sreevidya7

You can try this:

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

ENDIF

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703