IF Statement help

[jp357]

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.

[Thableaus]

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,

[jp357]

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

[Thableaus]

@jp357 

I edited my formula.

It was missing a "Cost" in your statement.

Try that again.

Cheers,

[jp357]

Tried that.

Now getting "Invalid Type in operator >=" message

[Thableaus]

@jp357 

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,

[jp357]

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

[Thableaus · Accepted solution]

@jp357 

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?

[jp357 · Accepted solution]

That worked, thank you very much for your help!

Greatly appreciated.