Ifelse(locate) giving generic syntax error

Question & Answer
1

Using ifelse(locate) to assign states to a region. The field contains two-letter state abbreviations. This works fine:

ifelse(locate(‘CT,ME,MA,NH,RI,VT’, {fac_practice_state})>0, ‘New England’, NULL)

But adding a second else condition fails with “the syntax of the calculated field expression is incorrect”, so this fails:

ifelse(
locate(‘CT,ME,MA,NH,RI,VT’, {fac_practice_state})>0, ‘New England’,
locate(‘WA,OR,CA,HI,AK’, {fac_practice_state})>0, ‘Pacific’,
NULL
)

I’ve used this pattern repeatedly in calculated fields but it fails every time with this particular calculation.
I can’t see any error:
ifelse(
locate(‘CT,ME,MA,NH,RI,VT’, {fac_practice_state})>0, ‘New England’,
locate(‘NJ,NY,PA’, {fac_practice_state})>0, ‘Middle Atlantic’,
locate(‘IL,IN,MI,OH,WI’, {fac_practice_state})>0, ‘East North Central’,
locate(‘IA,KS,MN,MO,NE,ND,SD’, {fac_practice_state})>0, ‘West North Central’,
locate(‘DE,FL,GA,MD,NC,SC,VA,WV’, {fac_practice_state})>0, ‘South Atlantic’,
locate(‘AL,KY,MS,TN’, {fac_practice_state})>0, ‘East South Central’,
locate(‘AR,LA,TX,OK’, {fac_practice_state})>0, ‘West South Central’,
locate(‘AZ,CO,NM,UT,WY,MT,ID,NV’, {fac_practice_state})>0, ‘Mountain’,
locate(‘WA,OR,CA,HI,AK’, {fac_practice_state})>0, ‘Pacific’,
NULL
)

2

These calculations work for me.

Have you tried deleting and creating a new one?

3

I deleted and copy/pasted the above three times in newly named calculated fields. It failed the first two times and worked the third. I have no idea… But mark it as asked and answered I guess.

1 Like