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

Question

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.

Answer 1

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()