IF contains formula

[mihir_mir_jb]

Hello All, Hope you are doing well. 

I have a quick question on IF contains formula, below is the formula I am trying to use but it is giving me parse error as soon as I enter endif in the end. Please help where am I going wrong

if contains([Asset Class], "Infrastructure", "IIF",
"Global Special Situations", "GSS", "Global Transport", "Income Fund","GTF","Global Transport Fund","SPF", "Strategic Property Fund","GTIF","Alternatives","GMO","Global Macro Opportunities","Hedge Fund", "Private Equity","Real Estate","Alternative") then "-1" ELSEIF Contains([Sub Asset Class],"Infrastructure", "IIF",
"Global Special Situations", "GSS", "Global Transport", "Income Fund","GTF","Global Transport Fund","SPF", "Strategic Property Fund","GTIF","Alternatives","GMO","Global Macro Opportunities","Hedge Fund", "Private Equity","Real Estate","Alternative") then "-1" ELSEIF Contains([Strategy],"Infrastructure", "IIF",
"Global Special Situations", "GSS", "Global Transport", "Income Fund","GTF","Global Transport Fund","SPF", "Strategic Property Fund","GTIF","Alternatives","GMO","Global Macro Opportunities","Hedge Fund", "Private Equity","Real Estate","Alternative") then "-1" ELSEIF Contains([Fund Name],"Infrastructure", "IIF",
"Global Special Situations", "GSS", "Global Transport", "Income Fund","GTF","Global Transport Fund","SPF", "Strategic Property Fund","GTIF","Alternatives","GMO","Global Macro Opportunities","Hedge Fund", "Private Equity","Real Estate","Alternative") then "-1" ELSEIF Contains([Subject],"Infrastructure", "IIF",
"Global Special Situations", "GSS", "Global Transport", "Income Fund","GTF","Global Transport Fund","SPF", "Strategic Property Fund","GTIF","Alternatives","GMO","Global Macro Opportunities","Hedge Fund", "Private Equity","Real Estate","Alternative") then "-1" ELSEIF CONTAINS([Subject/Notes], "Infrastructure", "IIF",
"Global Special Situations", "GSS", "Global Transport", "Income Fund","GTF","Global Transport Fund","SPF", "Strategic Property Fund","GTIF","Alternatives","GMO","Global Macro Opportunities","Hedge Fund", "Private Equity","Real Estate","Alternative") then "-1" ELSE "0" ENDIF

[ArnaldoSandoval]

Hi @mihir_mir_jb 

Yes, It fails because the syntax of the Contains function expects a single parameter:

This works:

if contains([Asset Class], "Infrastructure") Then "-1" Else "0" EndIf

This also works:

if contains([Asset Class], "Infrastructure", "IIF") then "-1" Else "0" EndIf

Although, it may not be checking for IIF in [Asset Class], it get confused and take "IIF" as the second parameter of the Contains function.

This fails:

if contains([Asset Class], "Infrastructure", "IIF", "GSS") then "-1" Else "0" EndIf

This is the help page with the Contains function: String Functions 

Hope this helps,

Arnaldo

This fails:

[mihir_mir_jb]

if I use the below, how can I continue with other columns like sub asset class, Strategy etc 

if contains([Asset Class], "Infrastructure") Then "-1" Else "0" EndIf

[Luke_C]

Hi @mihir_mir_jb 

Here's an updated formula based on your logic using the correct syntax for Contains(). Given the complexity a find and replace tool with a mapping table of the desired key words may be more efficient, but let me know how this works for you:

IF Contains([Asset Class], "Infrastructure") 
    or Contains([Asset Class], "IIF")
    or Contains([Asset Class], "Global Special Situations")
    or Contains([Asset Class], "GSS")
    or Contains([Asset Class], "Global Transport")
    or Contains([Asset Class], "Income Fund")
    or Contains([Asset Class], "GTF")
    or Contains([Asset Class], "Global Transport Fund")
    or Contains([Asset Class], "SPF")
    or Contains([Asset Class], "Strategic Property Fund")
    or Contains([Asset Class], "GTIF")
    or Contains([Asset Class], "Alternatives")
    or Contains([Asset Class], "GMO")
    or Contains([Asset Class], "Global Macro Opportunities")
    or Contains([Asset Class], "Hedge Fund")
    or Contains([Asset Class], "Private Equity")
    or Contains([Asset Class], "Real Estate")
    or Contains([Asset Class], "Alternative")
THEN
    "-1"
ELSEIF Contains([Sub Asset Class], "Infrastructure")
    or Contains([Sub Asset Class], "IIF")
    or Contains([Sub Asset Class], "Global Special Situations")
    or Contains([Sub Asset Class], "GSS")
    or Contains([Sub Asset Class], "Global Transport")
    or Contains([Sub Asset Class], "Income Fund")
    or Contains([Sub Asset Class], "GTF")
    or Contains([Sub Asset Class], "Global Transport Fund")
    or Contains([Sub Asset Class], "SPF")
    or Contains([Sub Asset Class], "Strategic Property Fund")
    or Contains([Sub Asset Class], "GTIF")
    or Contains([Sub Asset Class], "Alternatives")
    or Contains([Sub Asset Class], "GMO")
    or Contains([Sub Asset Class], "Global Macro Opportunities")
    or Contains([Sub Asset Class], "Hedge Fund")
    or Contains([Sub Asset Class], "Private Equity")
    or Contains([Sub Asset Class], "Real Estate")
    or Contains([Sub Asset Class], "Alternative")
THEN
    "-1"
ELSEIF Contains([Strategy], "Infrastructure")
    or Contains([Strategy], "IIF")
    or Contains([Strategy], "Global Special Situations")
    or Contains([Strategy], "GSS")
    or Contains([Strategy], "Global Transport")
    or Contains([Strategy], "Income Fund")
    or Contains([Strategy], "GTF")
    or Contains([Strategy], "Global Transport Fund")
    or Contains([Strategy], "SPF")
    or Contains([Strategy], "Strategic Property Fund")
    or Contains([Strategy], "GTIF")
    or Contains([Strategy], "Alternatives")
    or Contains([Strategy], "GMO")
    or Contains([Strategy], "Global Macro Opportunities")
    or Contains([Strategy], "Hedge Fund")
    or Contains([Strategy], "Private Equity")
    or Contains([Strategy], "Real Estate")
    or Contains([Strategy], "Alternative")
THEN
    "-1"
ELSEIF Contains([Fund Name], "Infrastructure")
    or Contains([Fund Name], "IIF")
    or Contains([Fund Name], "Global Special Situations")
    or Contains([Fund Name], "GSS")
    or Contains([Fund Name], "Global Transport")
    or Contains([Fund Name], "Income Fund")
    or Contains([Fund Name], "GTF")
    or Contains([Fund Name], "Global Transport Fund")
    or Contains([Fund Name], "SPF")
    or Contains([Fund Name], "Strategic Property Fund")
    or Contains([Fund Name], "GTIF")
    or Contains([Fund Name], "Alternatives")
    or Contains([Fund Name], "GMO")
    or Contains([Fund Name], "Global Macro Opportunities")
    or Contains([Fund Name], "Hedge Fund")
    or Contains([Fund Name], "Private Equity")
    or Contains([Fund Name], "Real Estate")
    or Contains([Fund Name], "Alternative")
THEN
    "-1"
ELSEIF Contains([Subject], "Infrastructure")
    or Contains([Subject], "IIF")
    or Contains([Subject], "Global Special Situations")
    or Contains([Subject], "GSS")
    or Contains([Subject], "Global Transport")
    or Contains([Subject], "Income Fund")
    or Contains([Subject], "GTF")
    or Contains([Subject], "Global Transport Fund")
    or Contains([Subject], "SPF")
    or Contains([Subject], "Strategic Property Fund")
    or Contains([Subject], "GTIF")
    or Contains([Subject], "Alternatives")
    or Contains([Subject], "GMO")
    or Contains([Subject], "Global Macro Opportunities")
    or Contains([Subject], "Hedge Fund")
    or Contains([Subject], "Private Equity")
    or Contains([Subject], "Real Estate")
    or Contains([Subject], "Alternative")
THEN
    "-1"
ELSEIF Contains([Subject/Notes], "Infrastructure")
    or Contains([Subject/Notes], "IIF")
    or Contains([Subject/Notes], "Global Special Situations")
    or Contains([Subject/Notes], "GSS")
    or Contains([Subject/Notes], "Global Transport")
    or Contains([Subject/Notes], "Income Fund")
    or Contains([Subject/Notes], "GTF")
    or Contains([Subject/Notes], "Global Transport Fund")
    or Contains([Subject/Notes], "SPF")
    or Contains([Subject/Notes], "Strategic Property Fund")
    or Contains([Subject/Notes], "GTIF")
    or Contains([Subject/Notes], "Alternatives")
    or Contains([Subject/Notes], "GMO")
    or Contains([Subject/Notes], "Global Macro Opportunities")
    or Contains([Subject/Notes], "Hedge Fund")
    or Contains([Subject/Notes], "Private Equity")
    or Contains([Subject/Notes], "Real Estate")
    or Contains([Subject/Notes], "Alternative")
THEN
    "-1"
ELSE
    "0"
ENDIF

[CSchaeffer]

Thanks for clarifying Contains only allows a single parameter.