IF Statements for Tenure

[tkaufman]

I am trying to create groups for different tenure categories, but I keep getting an error on my IF statements to create the groups.  I have tried multiple iterations to fix this, but below is the most recent. 

I get an error saying "Error: Formula (3): Parse Error at char(61): Malformed If Statement (Expression #1)"  I'm new to Alteryx so any help would be greatly appreciated! 

IF [Tenure]>'4' AND [TENURE]<'13' THEN "4-12 MONTHS"
ELSE IF [Tenure]>'12' AND [TENURE]<'25' THEN "13 - 24 MONTHS"
ELSE IF [Tenure]>'24' THEN "25 + MONTHS"
ENDIF

[binuacs · Accepted solution]

@tkaufman updated your formula

[rzdodson]

@tkaufman there are two recommendations that I would make for you:

1. Change your tenure field to be  a numeric field - this will assist Alteryx in better determining how associates should be bucketed.
2. Add an  else statement following your last elseif - this will correct the error you are receiving while also creating a built-in tester value (i.e. all others that fall outside of your defined buckets will just null out.

Final formula will look like this following the changes.

IF [Tenure]>4 AND [TENURE]<13 THEN "4-12 MONTHS"

ELSEIF [Tenure]>12 AND [TENURE]<25 THEN "13 - 24 MONTHS"
ELSEIF [Tenure]>24 THEN "25 + MONTHS"

else null()
ENDIF

EDIT: @binuacs beat me to the punch! :D

[cjaneczko]

The issue with your statement is the quotes around the numbers. Quotes are not used for Numeric fields only string fields. Remove the quotes or change your Tenure to a String type. Also you dont have a final Else before the endif. Need to add Else Null() or Else ' '.

[Raj]

So, the If statement have IF,then,else and Endif 
but your statement is missing else part so you can correct it by adding the else condition.

as suggested by pears you can add Null or any value you desire.

[grazitti_sapna]

A simple solution to this problem is to remove the quotation marks from your comparison formula. Additionally, it is advisable to insert an "ELSE" statement before the "ENDIF."
Thanks

[tkaufman]

Sorry it took so long to reply.  This worked PERFECTLY!  Thank you so much.

[tkaufman]

You are absolutely correct!  I could not figure out why when I was trying to recreate it, it wasn't working.  And I was missing that last else. Thank you so much for the help!