This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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
Solved! Go to Solution.
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.
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
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.
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.
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
Just chiming in to say that this is exactly right!
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