Null, blank, and empty values represent actual data points. They are not placeholders for query errors, with one exception: invalid calculated results, such as dividing a number by zero, are displayed as NULL. Trends AI uses -- as its dedicated placeholder for errors that occur during the query process, such as issues fetching data, the query returning no results, or an incomplete data set.
How NULL and Blank Values Are Displayed
When viewing a table or Chart, you may see data displayed as {blank}. This placeholder actually represents one of two distinct types of values:
NULL values, which signify missing or invalid entries. This includes calculated values that result in an error, such as division by zero. Trends AI also displays NULL when your data contains
n/a.Blank or empty values, such as an empty string of text or a string consisting only of whitespace (spaces, tabs).
Both of these are displayed as {blank}. However, if you apply a filter for {blank}, the filter will only be applied to the true NULL values. This means only the NULL values will be included in your results. This can make it difficult to determine what is happening if your data source contains both NULL and blank/empty values.
Note: Trends AI automatically adds the {Null} value to the filter options for all columns, even if there are no {Null} values present in that column.
Show NULL and Blank Values Differently
If you need to distinguish between NULL and blank values, you can add a formula to ensure they appear differently in your Charts and tables. In the example below, <text_column> refers to the column containing 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 display "null" where the actual value in the column is NULL. When the value is blank or an empty string, it will be displayed as "empty".
To Allow Filtering on Both NULL and Blank Values
If your goal is to maintain the same displayed format for both NULL and blank values, yet be able to filter on both using the term "{blank}", your formula structure will be slightly different. You can use a formula like this:
if ( strlen ( <text_column> ) = 0 ) then null else <text_column>
To get the desired result, use the new column created by this formula in your search instead of the original text column.
Filtering on Your Formula
After you create the custom formula that defines your desired values, you can filter on this new formula column in the Chart Builder. To do this, simply type the value {blank}, {empty}, or {null}, which will act as your filter. Then, you can use normal filter syntax, such as customer name = {empty} or department != {null}. Alternatively, you can filter directly from your search result table by left-clicking on a {blank}, {empty}, or {null} value, then right-clicking and selecting Show only "{value}".