Custom expression does not return values when the input value is NULL.

Custom expression does not return values when the input value is NULL.

book

Article ID: KB0081603

calendar_today

Updated On:

Products Versions
Spotfire Analyst All Versions

Description

When using a custom expression in a calculated column or axis expression, the expression does not return values when any of the input values are NULL or missing.

Issue/Introduction

Custom expression does not return values when the input value is NULL.

Resolution

If the required input cell values for the If() or CASE expression are NULL, then the expression will not be evaluated when the condition is looking for a string value. 

Example:

If([mycolumn]="a string value","True","False")

or:

case 
    when [mycolumn]="a string value" then "True"
    else "False"
end
In this case, if the value of [mycolumn] in a row is NULL, then the result of this Calculated Column will be NULL, not "False" as you might expect (since [mycolumn] does not equal to "a string value"). If you need to ensure that a value is returned even if the evaluated columns are NULL, then you can enter a case for the NULL value in your expression.

For example:

If([mycolumn] is not null and [mycolumn]="a string value","True","False")

or:

case
    when [mycolumn] is not null and [mycolumn]="a string value" then "True"
    else "False"
end

or:

case  
    when [mycolumn]="a string value" then "True"
    when [mycolumn] is null then "False"
    else "False"
end

In this case, even if the value of [mycolumn] in a row is NULL, the Calculated Column will return a value (all rows will have a value returned).
Powered by Wolken Software