Hello,
I am trying to make an IF statement to differentiate costs into categories. Ex: one category for costs ranging from 0 to 2500, one for 2500 to 5000, etc.
I was thinking it would look something like this:
IF (Cost) < 2500 THEN "Less than 2500"
ELSE (Cost) >= 2500 and <= 5000 THEN "2500-5000"
ENDIF
I tried this, but I keep getting an error message.
Any help would be greatly appreciated.
Solved! Go to Solution.
Hi @jp357
EDIT: In the 2nd line of your IF Statement, the "Cost" field was missing right by the AND clause.
You could change to this:
IF (Cost) < 2500 THEN "Less than 2500"
ELSEIF (Cost) >= 2500 and Cost <= 5000 THEN "2500-5000"
ELSE Null()
ENDIF
This meaning if it doesn't fit into any of these two categories, the result will be Null.
OR
IF (Cost) < 2500 THEN "Less than 2500"
ELSEIF (Cost) >= 2500 and Cost <= 5000 THEN "2500-5000"
ELSE "Greater than 5000"
ENDIF
Then anything that does not fall in the first two options would be "Greater than 5000"
Cheers,
Hey @Thableaus
Thank you very much for replying.
I tried those formulas but unfortunately I am getting "malformed if statement".
I am very new to alteryx and unsure of what could be going wrong here.
Under the formula, I currently have Data type set to "V_string"
Could this be why I'm getting malformed if statement?
Help would greatly be appreciated
Thanks
Tried that.
Now getting "Invalid Type in operator >=" message
Does your Cost field contain string values?
You can either change it to a Double type with a Select Tool before the Formula Tool.
Or you can add a ToNumber function in your IF Statement.
IF ToNumber(Cost) < 2500 THEN "Less than 2500"
ELSEIF ToNumber(Cost) >= 2500 and ToNumber(Cost) <= 5000 THEN "2500-5000"
ELSE Null()
ENDIF
Cheers,
Copied this formula exactly into alteryx.
Now getting "The formula resulted in a string but the field is numeric. Use ToNumber (...) if this is correct. (Expression #1).
Very confused as to why I am getting this message, since again I used the exact formula you just gave me. I also used the select tool to change data type to double
Thanks continuing to help
The new field must be a string field, since your response is a string
"Less than 2500"
"2500-5000"
So the Data Type needs to be V_String or V_WString.
Could you share a print screen of your Expression Window?
That worked, thank you very much for your help!
Greatly appreciated.