Custom expression with If() or Case function returns error "Invalid type for function call 'if'" in Spotfire

Product:TIBCO Spotfire Analyst
Versions:All Versions

Summary:
Custom expression with If() or Case function returns error "Invalid type for function call 'if'" in Spotfire

Details:
When writing a custom expression using the If() or Case function, the red exclamation point alert is displayed. You also cannot click the "OK" button, because the expression is deemed to be invalid, with the error "Invalid type for function call 'if'."

For example, using the If() function:

If([col1]=1,[col2],[col3])

The If() function takes three arguments. From the in-line function description:

Returns Arg2 if Arg1=true, and Arg3 if Arg1=false.

Example:
If(1 < 2, "Small", "Big")

It is also a requirement that Arg2 and Arg3 be of the same data type. If they are not, then you will see the "Invalid type for function call 'if'." error.

The same behavior will be seen with the Case function, if the resulting arguments (after "then" and "else") are not all of the same data type.

The reason for this limitation is that all output arguments must be valid within the final column data type, and so they must match the column's data type (integer, string, date, etc) since a column can only contain data of a single data type.

Resolution:

Confirm that all output arguments of the If() and Case functions are of the same data type. If the columns used in output arguments are different data types, then it is possible to use the built-in conversion functions to force them to be the same data type. 

For example, using the If() function:

If([col1]=1,String([col2]),String([col3]))

For example, using the Case function:

case
when [col1]=1 then String([col2])
else String([col3])
end