I am looking to create a new column in a dataset. The values in this new column are dependent on what is contained in another column.
I am looking to use an IF Contains statement but it is only returning me the 'ELSE' value even if a phrase is present in the other column.
My formula is below:
IF(Contains([Contact: Account Name: Sector],'Aerospace & Defence|Automotive|Business Services|Engineering & Construction|Manufacturing,'))
THEN "IM&A"
ELSEIF (Contains([Contact: Account Name: Sector],'Asset & Wealth Management|Banking & Capital Markets|Insurance|Regional, State, Local & City Government'))
THEN "FS"
ELSEIF (Contains([Contact: Account Name: Sector],'Central, National, Federal Government|G&PS - Other|Intl Government, Organisations'))
THEN "G&PS"
ELSEIF (Contains([Contact: Account Name: Sector],'Chemical|Energy|Metals|Mining|Power & Utilities'))
THEN "EUR"
ELSEIF (Contains([Contact: Account Name: Sector],'Consumer|Forest, Paper & Packaging|Hospitality & Leisure|Retail|Transportation & Logistics'))
THEN "CM"
ELSEIF (Contains([Contact: Account Name: Sector],'Health Services|Pharma & Life Sciences'))
THEN "HI"
ELSEIF (Contains([Contact: Account Name: Sector],'Media & Entertainment|Technology|Telecommunications'))
THEN "TMT"
ELSEIF (Contains([Contact: Account Name: Sector],'Private Equity|Real Estate|Sovereign Investment Funds'))
THEN "PE-SIF"
ELSE "ERROR"
ENDIF
I'd appreciate any input on this, right now the data is just returning the phrase "Error" as instructed by the ELSE statement
Solved! Go to Solution.
Hi @LFenton ,
does the column [Contact: Account Name: Sector] contain exactly the string "Aerospace & Defence|Automotive|Business Services|Engineering & Construction|Manufacturing," or only one of the piper separated entries, e.g. "Aerospace & Defence"?
If the column contains only one of the entries, try RegEx_Match instead of Contains.
Best,
Roland
@RolandSchubert Thanks very much, that's now working for most of my data. Just need to add a few extra values.
Appreciate the response!