Nested ifelse + locate + multiple string values

Question & Answer
1

Hi,
I’m trying to calculate the following:
I have multiple string values I am trying to locate in a column, and then assign a value.
I need to complete the above steps several times.

Example:
ifelse(locate(‘New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire’,{State}) > 0,‘Location A’, NULL) would be one statement.

each additional locate statement will have a shorter list of strings, and would set a different location, ie. Location B.

I tried the following but it didn’t work. I get a syntax error and I can’t figure it out.

ifelse(locate(‘New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire’,{State}) > 0, ‘LocationA’,
ifelse(locate(‘New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire’,{State}) > 0, ‘LocationB’,
ifelse(locate(‘Connecticut, Vermont, Maine’,{State}) > 0, ‘LocationC’,
ifelse(locate(‘New York, New Jersey, Connecticut’,{State}) > 0, ‘LocationD’,NULL)

2

Hello @fuse_r ,
Please try to use the following format:

ifelse(locate("New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire",State) > 0, "LocationA",
locate("New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire",State) > 0,"LocationB",
locate("Connecticut, Vermont, Maine",State) > 0, "LocationC",
locate("New York, New Jersey, Colorado",State) > 0, "LocationD","NOTHING")```
1 Like
3

Thank you Jose!
That solved my syntax problem (minus the 3 characters at the very end ```)

1 Like