Formula tool - Specific ranges

[gabrielmier222]

 Hi everyone,

I'm trying to modify the second expression below (for MEMBERS FS) so that the column returns a given value for a given range of values. 

I think the Boolean 'AND' is causing me error. Can someone help me identify what may be throwing off my expression and possibly how to fix?

Thanks in advance

[DataNath]

Hey @gabrielmier222, when using AND/OR statements, you need to re-state your field. So in your case it should be '...ELSE ToNumber([OR Numerical]) > 19999 AND ToNumber([OR Numerical]) < 30000 THEN...'

Also, if you want to add several possible outcomes, you'll want to use 'ELSEIF'. You only ever use 'ELSE' once, for your final alternative where you're basically saying "if none of the previous checks (in the elseifs) have been satisfied, do this". At the moment you have 2 'ELSE' operators and one of them has nothing after it, before the 'ENDIF' - you'll need to provide something here for Alteryx to do if neither of the ELSEIFS are satisfied.

Just a side note: If you're using the latest version of Alteryx (2023.1), then we now have the Between() function which would handle this a lot cleaner!

Would be '... ELSE Between(ToNumber([OR Numerical]), 20000, 29999) THEN...'

[lbardoe]

i think it looks like you have 2 else states when you should only one. So remove the ELSE before the ENDIF

[Rana_Kareem]

Hi @gabrielmier222 ..

After ELSE you must assign value to appear when the above conditions are not satisfied, you cannot write a condition in ELSE clause.

In your case, the conditional function would be like this:

IF ToNumber([OR Numerical]) < 20000

THEN '11x - Assets'

ELSEIF ToNumber([OR Numerical]) > 19999 AND ToNumber([OR Numerical]) < 30000

THEN '12X - Liabilities'

ELSE Null()

ENDIF

You can change the value after ELSE as you like.

And can find more information about conditional functions here:

https://help.alteryx.com/20231/designer/conditional-functions

Hope this helps..