How to check if a column is empty or not in TIBCO Spotfire client

How to check if a column is empty or not in TIBCO Spotfire client

book

Article ID: KB0072034

calendar_today

Updated On:

Products

Spotfire Analyst

Description

Consider the following dataset where the "Profession" column has a few empty rows.

Dataset:
Name Age Profession
sherlock 40 ""
Watson 38 Doctor

Issue/Introduction

This article describes how to determine if a column is empty or not in TIBCO Spotfire client.

Environment

All

Resolution

One method to identify the empty values is by using a calculated column with the following expression:
If(Len([Profession])=0,"Is Empty","Not Empty")

The above expression calculates the length of the column "Profession". If if the length is 0, it will display "Is Empty" otherwise it will result in "Not Empty".

Note: if the column could also contain null values - not just empty strings - those will not get the value "Is Empty". Null values are handled separately and will be displayed as "(Empty)". If empty and null values should be treated the same, consider adding the SN (Substitute Null) function to the expression to replace the null values: 
SN(If(Len([Profession])=0,"Is Empty","Not Empty"),"Is Empty")

If you have a numeric or date column and want to check for null values, the "Is Null" and "Is Not Null"  functions could be used:
If([MyDateColumn] Is Null,"A null value","Not a null value")