Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

IF Statement help

jp357
6 - Meteoroid

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.

8 REPLIES 8
Thableaus
17 - Castor
17 - Castor

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
6 - Meteoroid

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
17 - Castor
17 - Castor

@jp357 

 

I edited my formula.

It was missing a "Cost" in your statement.

 

Try that again.

 

Cheers,

jp357
6 - Meteoroid

Tried that.

Now getting "Invalid Type in operator >=" message

 

 

Thableaus
17 - Castor
17 - Castor

@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
6 - Meteoroid

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
17 - Castor
17 - Castor

@jp357 

 

The new field must be a string field, since your response is a string

"Less than 2500"

"2500-5000"

 

Sol12.JPG

 

So the Data Type needs to be V_String or V_WString.

 

Could you share a print screen of your Expression Window?

 

 

jp357
6 - Meteoroid

That worked, thank you very much for your help!

Greatly appreciated.

Labels