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
Solved! Go to Solution.
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.
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!
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
Solved thanks
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.
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