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,
Solved! Go to Solution.
If Column 1 is numeric, remove the quotes around the 1 and it should work!
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.
The following approaches should work:
IIF([Column1]!=1, "NONE", [Column3])
IIF(ToString([Column1])!="1", "NONE", [Column3])
IIF(ToNumber([Column1])!=1, "NONE", [Column3])
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?
Thank you!
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.
Brandon, thank you for the additional clarity - I will keep this in mind when building statements going forward.