community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Using Formula and If Statements

Hi,

 

I am very new to Alteryx and to be honest not the best at using formulas on excel. I am trying to write an If statement formula on Alteryx with multiple possible results. I.e If in the Column "Grade" we select "A1" then this new formula column will populate with "80", if we select "SA" then the cell populate with "190" etc. However when I have attempted this I am presented with the error message "Formula: invalid type in subtraction operator". Is this due to the fact that the grade field is currently set as data type "V String" and I want my new formula column to be numeric so I have set it as double, or is it an issue with how I have written the formula?

 

Here is the current formula I have created: 

 

IF [Grade] = "A1" THEN "80" ELSE IF [Grade] = "SA" THEN "190" ELSE IF [Grade] = "Contractor" THEN "34" ELSE IF [Grade] = "M" THEN "240" ELSE IF [Grade] - "SM" THEN "300" ELSE "?" ENDIF ENDIF ENDIF ENDIF ENDIF

 

Can anyone advise me as how to best sort this error? I require the output to be in numeric format.

 

Paul

 

 

Alteryx Certified Partner

Hi @pmaguire011 this syntax would work if your new column is a string field

 

IF [Grade] = "A1" THEN "80"

ELSEIF [Grade] = "SA" THEN "190"

ELSEIF [Grade] = "Contractor" THEN "34"

ELSEIF [Grade] = "M" THEN "240"

ELSEIF [Grade] - "SM" THEN "300"

ELSE "?"

ENDIF.

 

If you want a numeric field then the syntax should be 

 

IF [Grade] = "A1" THEN  80

ELSEIF [Grade] = "SA" THEN 190

ELSEIF [Grade] = "Contractor" THEN 34

ELSEIF [Grade] = "M" THEN 240

ELSEIF [Grade] - "SM" THEN 300

ELSE 0

ENDIF .

 

You Grade field should be a string field in the formula tool when you create a new field you want to select a numeric field type.

Highlighted
Pulsar

Hi @pmaguire011 

 

By typing quote marks around the numbers you want to return, you're returning text instead. Take out the quote marks around the numbers. You'll also need to use something else in place of the ? for the rest, so below I've replaced that with a zero. For fields to be numeric, all possibilities must be numbers

 

You don't need to nest IF THEN ELSE, because Alteryx has IF THEN ELSEIF....

 

 

Try this and let me know if it works (note that ELSEIF is one word not two)

IF [Grade] = "A1" THEN 80

ELSEIF [Grade] = "SA" THEN 190

ELSEIF [Grade] = "Contractor" THEN 34

ELSEIF [Grade] = "M" THEN 240

ELSEIF [Grade] - "SM" THEN 300

ELSE 0

ENDIF

 

Cheers!

Esther

Hi @EstherB47

 

This worked perfectly, thanks for that, this will be really helpful for me with similar issues!

Labels