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

Best Practices

Accepted answers

estherb47

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

All comments

JosephSerpis

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.

estherb47

Hi @pmaguire011

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

pmaguire011

Hi @EstherB47

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

AinsleyNigel

HI ,

What if there were multiple conditions in one statement for example above(have modified the above example with no reference to the same) ;

if [Grade]>=10000 THEN 'Qaulified' ELSEIF [Grade] >3999 and if [Grade]<10000 and if [Team] >0 THEN '1Team' ELSEif [Grade] <4000 and if [Team]>1 THEN '2Team' ELSEIF [Grade] <4000 and if [Team]=1 THEN 'No Team' Else 'Non Qualified'
Endif

Will this work , i get a parse error & a malformed if statement, pls suggest

Thanks

Nigel

ptjy

The problem is that you have a wrong symbol in the IF ELSE block.

ELSEIF [Grade] - "SM" THEN 300 is wrong, it should be ELSEIF [Grade] = "SM" THEN 300

Not a subtraction sign, but an equal to sign. That is why the error message said subtraction. Of course, the return data type is important.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703