The 'NullAsValue' Regular Statement

This article will cover the syntax of the ‘NullAsValue’ regular script statement.

This statement converts NULL values in given fields to specific values. This can be particularly useful for using NULLs in a filter or set analysis.

Statement syntax

Copy code


NullAsValue fieldlist;
Set NullValue = value;

Fieldlist	A comma separated list of fields where NULL values are to be replaced with new values. Wildcards can be used.
Value	Value to change NULL values to.

Example

In this example, all app NULL values are set to be changed to ‘Unknown’. Customers’ data containing many NULL values in the date of birth field is imported.

Copy code


NullAsValue *;
Set NullValue = 'Unknown';
CUSTOMERS:
LOAD
    id,
    firstName,
    lastName,
    email,
    dob
FROM [lib://Public:DataFiles/customers.xlsx]
(ooxml, embedded labels, table is Sheet1);