CASE/WHEN with null conditions behave differently for the first N-1 conditions vs the last condition in TIBCO Spotfire

Product:TIBCO Spotfire Analyst
Versions:All

Summary:
This article describes how null conditions behave differently for the first N-1 conditions vs the last condition in case/when statements in TIBCO Spotfire.

Details:
For better understanding,

•  Paste the following data from the clipboard:

Col1;Col2;
A;1;
A;;
C;1;
C;;

• Type the following Add calculated column expressions:
  
  My_Col:

  Case 
  when ([Col1]='A') and ([Col2]=1) then "X"
  when ([Col1]='C') and ([Col2]=1) then "Z"
  else "Dont print"
  end

Case  
when ([Col1]='C') and ([Col2]=1) then "Z" 
when ([Col1]='A') and ([Col2]=1) then "X"
else "Dont print"
end

• The below output is displayed with Null result in one case, "Dont print" in the other case, where the order of the when clauses should not matter.

Resolution:

• In Spotfire, null is treated as false, in all but the last condition. This is considered a defect.
• For now, a workaround is to add an additional dummy statement  as the very last WHEN condition, to make the ELSE clause work as expected. In the example below, "when false then "dummy"" was added for this purpose.

           Correct Output:

Case
when ([Col1]='A') and ([Col2]=1) then "X" 
when ([Col1]='C') and ([Col2]=1) then "Z" 
when false then "dummy"
else "dont print"
end