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 not true. This is notably different than being false, but in expressions that rely on a boolean result, such as if(), a null value corresponds to 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: