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

Using Formula and If Statements

pmaguire011
5 - Atom

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

 

 

8 REPLIES 8
JosephSerpis
17 - Castor
17 - Castor

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
15 - Aurora
15 - Aurora

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

pmaguire011
5 - Atom

Hi @EstherB47

 

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

AinsleyNigel
5 - Atom

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

 

AinsleyNigel
5 - Atom

HI ,

 

Attached is a file with some sample data.

 

Pls help.

 

Thanks

Nigel

 

AinsleyNigel
5 - Atom

Solved thanks

ptjy
6 - Meteoroid

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.

KK12
7 - Meteor

Hi @Nigel1

 

Use the formula as follows and you will not get the parse error:

 

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

 

You have use if nultiple times which is not needed and also, And needs to be selected as "Boolean AND - Keyword"

 

Hope it helps

 

Regards,

Kirti

Labels