Multi-Conditional ELSEIF statement

[MHS]

Hello Everyone, 

I am new to Alteryx and am running into an issue with my ELSEIF statement to make a new column in the data set.  I believe that most of it is structured correctly; however, when I start to close out the statement with ELSE and ENDIF my text goes from a colorful array to black and various errors are thrown at me once run.  If anyone could help it would be greatly appreciated.

IF [Duration]<=30 THEN "Less than 30"
ELSEIF [Duration]>30 and [Duration]<=60 THEN "30-60 Days"
ELSEIF [Duration]>60 and [Duration]<=90 THEN "60-90 Days"
ELSEIF [Duration]>90 and [Duration]<=120 THEN "90-120 Days"
ELSEIF [Duration]>120 and [Duration]<=150 THEN "120-150 Days"
ELSEIF [Duration]>150 and [Duration]<=180 THEN "150-180 Days"
ELSEIF [Duration]>180 and [Duration]<=210 THEN "180-210 Days"
ELSEIF [Duration]>210 and [Duration]<=240 THEN "210-240 Days"
ELSEIF [Duration]>240 and [Duration]<=270 THEN "240-270 Days" ELSE
"270+ Days"
ENDIF

Thanks!

[Thableaus]

Hi @MHS 

Is your [Duration] Column a String or an Int/Double Type?

Cheers,

[JosephSerpis · Accepted solution]

Hi @MHS is your [Duration] field a string or numeric field? Also are you creating a new field or updating in your formula tool? I used your if statement on some mock data and it worked. Also you can simplify your if statement to achieve the same result I have attached a workflow.

[MHS]

It is currently a string.

[MHS]

Hi JS420, 

Your formula makes much more sense, I was trying to follow some of the trainings when I structured the statement.  I used your formula and changed the field to an integer and it worked!  Thanks for all the help everyone!

[JosephSerpis]

Hi @MHS you could use this tonumber to convert a string field within the formula as well so your original If statement would have to change to this. 

IF ToNumber([Duration])<=30 THEN "Less than 30"
ELSEIF ToNumber([Duration])>30 and ToNumber([Duration])<=60 THEN "30-60 Days"
ELSEIF ToNumber([Duration])>60 and ToNumber([Duration])<=90 THEN "60-90 Days"
ELSEIF ToNumber([Duration])>90 and ToNumber([Duration])<=120 THEN "90-120 Days"
ELSEIF ToNumber([Duration])>120 and ToNumber([Duration])<=150 THEN "120-150 Days"
ELSEIF ToNumber([Duration])>150 and ToNumber([Duration])<=180 THEN "150-180 Days"
ELSEIF ToNumber([Duration])>180 and ToNumber([Duration])<=210 THEN "180-210 Days"
ELSEIF ToNumber([Duration])>210 and ToNumber([Duration])<=240 THEN "210-240 Days"
ELSEIF ToNumber([Duration])>240 and ToNumber([Duration])<=270 THEN "240-270 Days" ELSE
"270+ Days"
ENDIF

However you found the easier approach of just changing the field to an numeric field but just wanted to highlight a different way.

[estherb47]

Hello @MHS 

A little late to the party here, but you can simplify your formula a lot. The IF THEN ELSE for numeric comparisons doesn't need a ceiling. So, instead of the > AND  < logic, you can trust that if a value fails one line, you don't need to run that test again. That is, if a number is between 31 and 60, you'll get 30-60 days by just using <=60

Please try this out:

IF ToNumber([Duration])<=30 THEN "Less than 30"
ELSEIF ToNumber([Duration])<=60 THEN "30-60 Days"
ELSEIF ToNumber([Duration])<=90 THEN "60-90 Days"
ELSEIF ToNumber([Duration])<=120 THEN "90-120 Days"
ELSEIF  ToNumber([Duration])<=150 THEN "120-150 Days"
ELSEIF ToNumber([Duration])<=180 THEN "150-180 Days"
ELSEIF ToNumber([Duration])<=210 THEN "180-210 Days"
ELSEIF ToNumber([Duration])<=240 THEN "210-240 Days"
ELSEIF ToNumber([Duration])<=270 THEN "240-270 Days"

ELSE
"270+ Days"
ENDIF

Let me know if that works.

Cheers!

Esther

[OlgaO]

So, the formula would be executed step by step? It is very important, Otherwise it would bring TRUE values for several conditions in the statement  

For example , 100 days would be TRUE for both below conditions:

...ELSEIF [Duration]<=120 THEN "90-120 Days"
ELSEIF [Duration]<=150 THEN "120-150 Days"..