Concept: NULL Values in Tables and Datasets
If a field value in a table or dataset is empty, it's considered a NULL value. When you enable a table for analysis or publish a dataset, Workday replaces all NULL values with a default value in the Prism data source. Prism data sources, discovery boards, and reports have no concept of NULL values.
How Tables Process NULL Values
A value can be NULL because the raw data from the source is missing values for a particular field.
How Datasets Process NULL Values
A value can be NULL for these reasons:
- The raw data from the source is missing values for a particular field.
- The raw data from an external data source can't be parsed for the specified field type.
- A Prism calculated field expression returns an empty or invalid result.
- The dataset contains a Join stage and:
- You configure an outer join (you include all rows from at least one of the dataset pipelines).
- There's no match from 1 pipeline to the other.
Default Values by Field Type
Workday replaces NULLs with these values in a Prism data source:
Field Type | Value in Prism Data Source |
|---|---|
Boolean | False |
Currency | 0 (with an empty currency code) |
Date | (Blank)
If the date value in a table that is enabled for analysis is 0000-12-31T23:59:59.999Z , then Workday displays inconsistent result. |
Numeric, Double, Integer, Long | 0 |
Instance | (Blank) |
Multi-Instance | (Blank) |
Text | (Blank) |
NULL Values in Expressions
Prism Analytics uses expressions in Prism calculated fields, Filter stages, and when defining custom example data in a derived dataset. The way datasets treat NULL values in expressions is consistent with SQL standard. That means:
- Arithmetic calculations on numeric fields that involve a NULL return NULL. Example:5 + NULLreturns NULL.
- Comparison operations that result in a Boolean field that involve a NULL return NULL. Example:5 > NULLreturns NULL.
Currency fields have 2 components to the field value (the currency code and currency value), and as a result, they handle NULLs a little differently than numeric fields in arithmetic calculations.
- Addition and subtraction calculations on Currency fields that involve a NULL return NULL. Example:TO_CURRENCY("5.00 USD") + NULLreturns NULL.
- Addition and subtraction calculations on Currency values that use different currency codes return NULL. Example:TO_CURRENCY("5.00 USD") + TO_CURRENCY("5.00 EUR")returns NULL.
You can't use Currency fields in comparison operations. However, you can test for NULL values by using this syntax:
valueIS NULLvalueIS NOT NULL
For more details about boolean expressions, see Reference: Boolean Expressions.
NULL Values in Filter Stages
When you filter on a field using an expression similar to
[field] != MyValue
, Workday filters out all records that have a value of MyValue or NULL. The logic behind this behavior is:
- Comparison operators that involve a NULL return NULL.
- Workday evaluates NULL boolean values as False.
NULL Values in Group By Stages
When you create a Group By stage, you select a dataset field for grouping and another field for summarizing. How Workday treats NULL values depends on how you use the field:
- For grouping fields, any NULL values result in their own group.
- For summarization fields, NULL handling depends on the summarization type.
Summarization Type | Description |
|---|---|
Average | Returns the average of all valid numeric values in the group. It sums all values in the provided expression and divides by the number of valid (not NULL) rows.
If a Currency field contains multiple currency codes, the result is NULL. |
Count | Returns the number of all rows in a group (counts all values, both NULL and non-NULL).
If a Currency field contains multiple currency codes, the result is NULL. |
Maximum | Returns the greatest of all non-NULL values, and NULL if all values are NULL.
If a Currency field contains multiple currency codes, the result is NULL. |
Minimum | Returns the lowest of all non-NULL values, and NULL if all values are NULL.
If a Currency field contains multiple currency codes, the result is NULL. |
Sum | Returns the total of all non-NULL values, and NULL if all values are NULL.
If a Currency field contains multiple currency codes, the result is NULL. |