Search code examples
filterdashboardspotfiresubstring

Spotfire: Can you write a case statement based on a column containing a substring

I want to create a calculated column based on a substring. I can't find the syntax to do something like the following

 case 
 when [ProjectName] contains "substring" then [Value]
 end

For example, when the [ProjectName] contains "overhead" then "overhead" [ProjectName] would be equal to "Project 1 Overhead", "Project 2 billable", or something like that.

Solution

  • The easiest solution would be to use FIND() with an IF() or CASE() statement.

    If(Find("overhead",Lower([ProjectName])) >0,[Value],"FALSE")

CASE 
    WHEN Find("overhead",Lower([ProjectName])) >0 THEN [Value]
END

    Just remember, Find() is case sensitive.

    If this is a step in replacing the sub-string with a value, then you would want to use RXReplace()