Operator help for IF Condition

Currently trying to populate column 3 based on value in column 1 - if column 1 not equal to 1 populate column 3 with "NONE"

My Statement is as follows:

IIF([Column1]!="1", "NONE", [Column3])

The error I am getting is saying there is an invalid operator of "!=", however I often use this operator in other conditional statements. Where am I going wrong?

I should add, column 1 is not a boolean data type and confirmed Alteryx is not comprehending it as such via select tool.

thank you,

Developer

Accepted answers

alexnajm

If Column 1 is numeric, remove the quotes around the 1 and it should work!

BrandonB

The following approaches should work:

IIF([Column1]!=1, "NONE", [Column3])

IIF(ToString([Column1])!="1", "NONE", [Column3])

IIF(ToNumber([Column1])!=1, "NONE", [Column3])

BrandonB

Yep so to elaborate

IIF([Column1]!=1, "NONE", [Column3]) - This formula works if column 1 is a numeric data type and only if it is a numeric data type. If the data type changes for some reason, this formula will throw a syntax error.

IIF(ToString([Column1])!="1", "NONE", [Column3]) - This formula works with both string and numeric data types because the actual expression turns column 1 values into strings regardless of whether they are already strings or if they are numbers. This is the most flexible format because it can deal with multiple formats.

IIF(ToNumber([Column1])!=1, "NONE", [Column3]) - This formula technically works with both string and numeric data types because it converts the values in column 1 into a number prior to evaluating. The only downside here is scenarios where text values/characters are attempted to be converted into numbers which isn't always ideal.

Therefore I would probably use the second expression here if there is any chance of column1 changing from number to string or vice versa in the future.

All comments

alexnajm

If Column 1 is numeric, remove the quotes around the 1 and it should work!

BrandonB

What data type is Column1? If it is a number then you don't need quotes around the number 1. The value only requires quotes around it if it is evaluating a string data type.

BrandonB

The following approaches should work:

IIF([Column1]!=1, "NONE", [Column3])

IIF(ToString([Column1])!="1", "NONE", [Column3])

IIF(ToNumber([Column1])!=1, "NONE", [Column3])

CDIns

Thank you!

Just to confirm I am following correctly: The first statement would work if column 1 is numeric, is the second statement basically saying you can keep the "" around 1 but first you need to convert column one from numeric to string JUST for this formula? and the third statement would work if column 1 was a string but I didn't want to include the "" around 1?

CDIns

Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703