Nulls and Nulls in Measures

Using Nulls In SAQL

You can specify a null constant almost anywhere a constant string or number can appear in SAQL, with the following exceptions and clarifications.

Typing

null is not typed. It is inferred from context. For example, null + 4 is a number. A SAQL syntax error will be generated when a type cannot be infered.

Filters

When a filter expression evaluates to null, the row is filtered out.

• Lists

  foo in [null, "bar"] is handled like foo == null or foo == "bar".

• Ranges

  filter q by dim in [null.."myvalue"] is handled as (dim>=null and dim<=7)

Unsupported

null is not supported in the following contexts:

• Offset
• Limit
• dateRelative
• dateRange
• Windowing range
• Trim (second argument)

Null Values in Measures

Null measure handling lets customers distinguish between null and non-null—for example, the number 0—values in their numerical data. SAQL support for null measures facilitates this customer preference; for example, when using aggregation, comparison, and math functions, and for order by or group by clauses.

When you create or update a dataset, for example through your dataflow or a CSV upload, any blank measure values in your data are replaced with specific values. Analytics uses the default values specified in your dataflow or CSV metadata file to replace blank values.

Replacing blank values with zeros can be problematic for a number of reasons. Take the example of data with customer satisfaction scores, where some customers have not responded. Calculated values such as average and minimum are correct in the source data, but when blank values are replaced with zeros when the dataset is created, the resulting calculations are incorrect.

Null measure handling lets you specify defaults using the special "null" value in your dataflows and CSV metadata files. When no default value is specified, Analytics replaces blanks with null values.

For more information on null measure handling and how to set it up, see the Analytics Data Integration Guide.