...
- Null values are visually represented as missing values.
- In the data quality bar, null and missing values are represented in the dark bar (missing values).
- Computationally, they are different types of values.
- Most functions applied to null and missing values return the same results.
- For example, the
ISMISSING
function returnstrue
for null and missing values. - However, the
ISNULL
function returnstrue
for a null value andfalse
for a missing value. See below.
- For example, the
- If you use a function to generate null values, they are displayed as missing values, although they are recorded as nulls.
For example, the following transform generates a column of null values, which are represented as missing values in the data quality bar.
D code showNote true derive type:single value:NULL()
- Most functions applied to null and missing values return the same results.
- When a set of results is generated, both null and missing values are written as missing values, unless the output format has a specific schema associated with it.
Info | |
---|---|
NOTE: When a recipe containing a user-defined function is applied to text data, any null characters cause records to be truncated by the running environment during
|
Locate null values
Null values are displayed with missing values in the Missing values category of the data quality bar (in black).
You can use the following transform to distinguish between null and missing values. This transform generates a new column of values, which are set to true
if the value in isActive
is a null value:
Tip |
---|
Tip: You can use this transform and a subsequent sorting step on the generated column to filter for null values. |
D code |
---|
derive type:single value:ISNULL(isActive) |
...
Fix null values
See Find Missing Data.
Null values in transformations
Functions:
- Applying a null value as an input to a scalar function returns a null value, propagating the null value.
- In aggregate or window functions, null values are ignored, as a single null value could corrupt an entire column of calculations.
Transforms:
- In a join, a null value in one dataset never matches with a null value in another dataset. Rows with null values in join key columns are never included in the output.
Write null values
If needed, you can write a null value to a set of data. In the following example, all missing values in a column are replaced by nulls, using the NULL
function:
...