If you have NULL values in your column, you won’t be able to perform functions on it or combine it with another column. NULL results will be ignored and excluded from your results. NULL records being excluded from the result set is a result of what is called three-value logic (3VL) in SQL. Please refer to the following page for a detailed explanation.
Replacing NULL values with zero
If you’d like to replace all NULL values within a column with another value, the coalesce () function will easily let you do this. This function accepts two or more arguments and returns the first non-NULL argument. The function must have at least 2 arguments.
coalesce("column", replace_value, ….)
column: The column where you want NULL values to be replaced by the specified replace_value.
replace_value: The column or values you want to replace your NULL values with. You can have more than one replace_value.
To replace NULL values with coalesce function in the Data Pipeline, click the +Add Transformation button and choose Add Column from the menu options. To replace any column name that could contain NULL values with 0, use the following formula (replacing Column Name with the name of your column):
coalesce("Column Name", 0)
You’ll likely want to hide your original column by adding a Hide Columns step after the Add Column step in the Data Pipeline.
Tip: to minimize the number of Pipeline steps, you can stack multiple functions as part of your formula like in the screenshot above.
Replacing NULL and blank values
If the coalesce function doesn’t fully replace all empty values in your query results, your dataset may contain both NULL and blank values. To account for both cases, use a case statement function over coalesce.
To do this, you can either use an Add Column or an Edit Column Pipeline step and follow these steps:
- Select the column to which you want to add zero fill.
- Select the formula type as Custom formula.
Enter the following case statement in the Formula field (replacing Column Name with the name of your column):
CASE WHEN “Column Name” IS NULL THEN 0 WHEN “Column Name” = ‘’ THEN 0 ELSE “Column Name” END
Replacing NULL values using another column
You can also replace NULL values found in one column with another column’s values. This would be carried out at the row level, meaning, if column A’s value is NULL in row 3 and you want to replace that NULL value with a value from column B, the function would replace the NULL from column A with the value from column B in row 3.
To see an example of how to do this in the Pipeline, check out the video below: