The case() statement in Spotfire is a useful way to create complex if-then calculations, but there are situations that can result in errors or unexpected outcomes. Two of these are:
1. Error: Invalid type for function call "if"' 2. Else clause not executed
Issue/Introduction
Some common problems that occur when using a case() statement
Resolution
1. Error: Invalid type for function call "if"
This happens when the clauses of the case() statement result in different data types. For example:
case when Arg1="a" then Arg2(String) when Arg1="b" then Arg3(Real) else Arg3(Real) end
The mix of String and Real results are not allowed. Restructure the clauses so that they all result in the same data type.
2. Else clause not executed
This can happen when clause of the case() statement cannot be executed. For example:
case when Integer(Arg1) < 10 then Arg2 when Integer(Arg1)>= 10 then Arg3 else Arg4 end
If Arg1 is a String column but most values are numbers, then Integer(Arg1) produces an Integer. However, for any cells in the column contain only alpha values, that clause produces a Null value, and the program cannot compute the final 'else' clause. Adding a final 'when' clause that can be computed will allow Spotfire to compute the 'else' clause. It can be something as simple as:
when false then "blank". So the full case() statement would look like:
case when Integer(Arg1) < 10 then Arg2 when Integer(Arg1)>= 10 then Arg3 when false then "blank" else Arg4 end