Null Value Handling

What Is a Null Value?

The following cases may all be understood as null values:

• null: Used to indicate that a data item contains no valid data, data is missing, or data is unknown. It can exist in all data types. In database tables of different types, it may appear as blank or as null.
• Empty string: An empty text string that exists only in text types. Because it is invisible, it may be confused with null.
• Text null: Actually the text value 'null', including uppercase, lowercase, and mixed-case formats. In some scenarios, users may replace null values in text fields with the text string 'null' when writing data to the database, in order to distinguish them from data with values.

Impact of Null Values

1. In any expression or function formula, if any part of the value is null, the final result usually returns null. For example, in CONCAT([Field A], [Field B]), if a row contains a null value, the result for that row returns null. A condition such as null = null returns null instead of true.
2. Counting with COUNT([Field]) / COUNT(DISTINCT[Field]) filters out null values, but does not filter out empty strings. The result of COUNT(DISTINCT[Field]) is 1 less than the actual result.
3. When converting columns to rows in ETL, if a field to be converted is null, the entire row is filtered out, causing data loss.
4. If the field type and value content do not match, the data is displayed as null, which can mislead users into thinking there is no data.
5. If a dataset has deduplication primary keys and data is incrementally updated, duplicate data may still exist because null values in primary keys cannot be deduplicated.

Null Value Identification and Handling

Dataset

On the overview page, go to Filter Data, select a field, and choose the Selection type. Null values are displayed as (null), empty strings are displayed as blanks, and other text-type null values are displayed as text. If you need to set a deduplication primary key, make sure that fields used as deduplication primary keys do not contain null values. In datasets, null values in fields cannot be directly replaced or processed. It is recommended to process them in ETL, or create calculated fields and use functions to handle them.

ETL

1. Data profiling, available in version 4.9 and later: add a Data Profiling node after the required node, select a field, and view the overview of null values and enumerated values for that field.

1. Null value replacement: replace null values with other values. Value replacement: replace empty strings, or null, with other values. The replacement target value must be consistent with the field's own type.

3. For cases where null values need to be judged and replaced according to certain logic, add a calculated field and use Spark SQL functions to process them. Common null value handling functions are as follows:

Purpose	Function	Example	Result
Determine whether a value is null	isnull([expr]); [expr] is null		true/false
Determine whether a value is not null	isnotnull([expr]); [expr] is not null		true/false
Replace the null value in expr1 with expr2	ifnull([expr1], [expr2])	ifnull(null,0)	0
	nvl([expr1], [expr2])	nvl(null,0)	0
If expr1 is not null, return expr2; otherwise return expr3	nvl2([expr1], [expr2], [expr3])	nvl2(NULL, 2, 1)	1
Return the first non-null value	coalesce([expr1], [expr2], ...)	coalesce(NULL, 1, NULL)	1
If two expr values are the same, return null; otherwise return expr1	nullif([expr1], [expr2])	nullif(2, 2)	null
Determine whether a value is an empty string	[expr]='' [expr] <>''		true/false
Determine whether two values are equal; return true when both are null, and false when one is null	[expr1] <=> [expr2]	true <=> NULL	false

Card

If you do not want calculation results that are null to appear as blank, set null values to display as other values or text in the Special Values section of the style panel on the right side of the card.

Note

Special value settings only change the display effect. They do not truly replace null values with other data or make them participate in calculations. For example, when sorting a numeric field that contains null values, null values can only be placed at the beginning or end. Setting null values to display as 0 does not sort the corresponding data between positive and negative numbers.

If null values need to participate in calculations, process them in ETL first, or create a calculated field in the card. For non-direct datasets, use the Spark functions listed above and then use the new field for calculations. For direct datasets, use the corresponding database functions.

Case

The field is displayed as null, but why does judging and replacing null values not take effect?

Formula for Empty Date Replacement: ifnull([日期2],date('2099-01-01'))

Cause analysis: The formula usage itself is correct. After checking the field Date 2, it is found to be a newly created calculated field. The field does not use a function and directly references another field, Date 1, then sets the format to Date, as shown below. Date 1 is a date composed of numbers, with the type String.

The user's original intention is to convert a text-type date to a standard date format and then replace null values. However, fields of different types must be converted with SQL functions to take effect. Simply modifying the field type in a new field does not truly take effect. When the real type conflicts with the display type, the system displays null by default, but the value actually exists. At this point, verifying with the function isnull([日期2]) also returns a non-null result, namely false.

Solution: Modify the Date 2 formula to to_date([日期1],'yyyyMMdd'). The validation result is shown below.