Null Values in Spectre
Null indicates "no data". Here are some ways to get a null value in Spectre:
- A blank field, that is a zero-length field, in a source file read by Spectre build
- A value flagged as null using the nulls tag in Spectre build
- A NULL value in an ODBC table read by Spectre build
- The result of a summary expression in a dive window using multiple inputs, when the dimension value is not present in one of the inputs (that is, a multi-level multi-model)
- The result of a summary expression when the working set is empty
- The expression `null`
- The expression `""`
You can check for null using the is_null() and notnull() functions, and use the coalesce() function to substitute alternative values for null.
Every null value has a type, based on what the type of the value would be if it were not null. Null behaves differently depending on how it is used and what type it is:
- For numeric types (integer, fixed100, and double), null is sometimes treated as zero, but there are exceptions depending on how it is used
- For strings, null is the empty string and vice versa, for example, `"" == null` is true
- For dates and periods, null is never treated as similar to a normal date or period, it is always considered special
- For boolean types, null is treated as false
When a null is of a numeric type (integer, fixed100, double), the following behaviors apply:
-
The + and - operators treat null as zero, but if both operands are null, the result is null.
For example, `null + 5` returns 5; `4 - null` returns 4; `null + null` returns null.
-
The * operator returns null if either operand is null.
For example, `null * 5` returns null.
-
The / operator returns unknown if the denominator is null.
For example, `5 / null` returns unknown.
-
If the denominator is not null, the / operator treats null numerators as zero.
For example, `null / 5` returns null.
-
Null is equal to null.
For example, `null = null` and `null <= null` return true; `null < null returns false.
-
Null is treated as zero in numeric comparisons.
For example, `null > -10` returns true; 0 = null returns true.
-
The mod() function behaves like the / operator.
For example, `mod(5, null)` returns unknown; `mod(null, 5)` returns null.
-
The min() and max() functions skip nulls, returning null only if all arguments are null.
For example, `max(null, -1)` returns -1; `min(null, null, null)` returns null.
-
Math functions that return zero or null when the operand is zero, return null when the operand is null.
For example, `sqrt(null)`, `round(null)`, and `abs(null)` return null.
-
Other functions treat null as zero.
For example, `pow(10, null)` returns 1; `log(null)` returns unknown; `pct_var(null, 5)` returns 0.
For strings, null and the empty string ("") are the same. For example:
- `"x" + null` returns "x"
- `null = ""` returns true
- `date("")` and `date(null)` return null
- `lpad(null, 2, ".")` returns ".."
- `startswith("xyz", null)` returns true
- `damerau(null, null)` returns true
When a null is a date or period type, the following behaviors apply:
-
Equality operators consider null dates equal to other null dates.
For example, `null = null` returns true.
-
Comparisons return false when either argument is null.
For example, `null > date("2015/01/01")` and `null <= date("2015/01/01")` return false.
-
Functions that return a date when the operand is a date, return null when the operand is null.
For example, `offset(null, "month", 2)` returns null.
-
Functions that return a boolean when the operand is a date, return false when the operand is null.
For example, `ytd(null, today())` returns false.
-
Functions that return something else when the operand is a date, return unknown when the operand is null.
For example, `month_name(null)` and `year(null)` return unknown.
The following behaviors apply to summary functions:
-
The functions count(), ucount(), and dimcount() treat null as just another value, distinct from zero.
For example, dimcount over [null, "x", "x", null] returns 2.
-
The functions count(), ucount(), and dimcount() return 0 when the working set is empty.
For example, count over [] returns 0.
-
Other summary functions ignore null values and return null when the working set is empty.
For example, average over [1, null, 3, null] returns 2; sum of [] returns null; and min of [null, null] returns null.
- In summary columns, that is the non-dimension columns, null and unknown both display as blank values.
- In dimension columns, null is the blank value and unknown displays as <unknown>.
See also: