Filter on null, blank, or empty values

Filtering on NULL and empty values is a special case, and can be tricky if your data contains more than one of these. You can use this method to see what’s really going on with these types of values, and to get the filtering behavior you want.

Null, blank, and empty values are actual values in your data. They are not placeholders for errors in the query, with 1 exception: invalid values, such as dividing by 0, are displayed as NULL. -- is ThoughtSpot’s placeholder for errors in the query. These errors may include issues fetching the data query, the query returning no data, or the data set not being complete.

How NULL and blank values are displayed

When you view a table or chart, you may see values that appear as {blank}. These can actually be one of two types of values:

  • NULL values, which are essentially missing or invalid (for example, dividing by 0) values. ThoughtSpot also shows NULL if you have n/a in your data.

  • blank or empty values, like an empty string of text or a string containing only whitespace (spaces, tabs).

Both of these types of values are represented as {blank}, but if you filter on {blank}, the filter will apply to only the NULL values. That is, only the NULL values will be included in your result. It can be hard to tell what’s going on if you have a data source that contains both NULL and blank/empty values.

ThoughtSpot automatically adds the {Null} value to the filter options for all columns, even if there are no {Null} values in the column. If you do not want ThoughtSpot to automatically add the {Null} value to the filter options for columns without {Null} values, contact ThoughtSpot Support.

Show NULL and blank values differently

If you need to differentiate between NULL and blank values, you can add a formula to make them appear differently in charts and tables. In this example, we’ll use <text_column> to refer to the text column which contains both NULL and blank values:

if ( strlen ( <text_column> ) = 0 ) then if ( isnull ( <text_column> ) ) then 'null' else 'empty' else <text_column>

This formula will show "null" where the value contained in the column is actually NULL. When the value is blank or empty, it will show up as "empty".

To allow filtering on both NULL and blank values

If you want to keep the same display format for NULL and blank values, but be able to filter on both using "{blank}", your formula will be slightly different. You can use a formula like:

if ( strlen ( <text_column> ) = 0 ) then null else <text_column>

Use the filter you created instead of the original text column in your search to get the desired result.

Filtering on your formula

After creating the formula for what you want to do, you can filter on the formula column you created in the search bar by typing the value {blank}, {empty}, or {null}, which will act as a filter. Then, you can type normal filter syntax, such as customer name = {empty} or department != {null}. Or you can filter by left-clicking on a {blank}, {empty}, or {null} value in your search result table, then right-clicking and selecting Show only "{value}".

Related information

Was this page helpful?
×