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 Prism Calculated Fields
The way datasets treat NULL values in Prism calculated fields 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
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. |