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

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

 

Some common problems that occur when using a case() statement