The NULL (or written Null or null) value in LityxIQ can be thought of as an unknown or missing data value. It is analogous to NULL values you may be familiar with in SQL tables, or NA values in other applications. Any data value for any variable in a dataset can have the value NULL. NULL values should not be confused with zero values or empty strings. They are different, and represent an unknown data value. NULL values in datasets are not uncommon, so it is important to understand how they are created and processed in LityxIQ datasets.
Importing NULL Values from a Raw Data Source
For Raw datasets, NULL values will be imported from the raw data source if they exist there. For example a delimited text file may contain NULL values as indicated by a specified string that appears in the file (see the Null String setting description in https://support.lityxiq.com/678242-Import-a-Delimited-Text-File). NULL values in SQL tables will be imported directly as NULLs in LityxIQ.
Creating NULL Values in a Derived Dataset
The process of creating new fields in a derived dataset may result in null values. NULL values may be created automatically, created as desired, or created based on conditions that exist in the data. For example, any arithmetic operations on an existing NULL value will result in another NULL. In some applications, dividing by zero will directly create a NULL value as the result. However, in LityxIQ, an error will be created. See https://support.lityxiq.com/670356-Zero-in-Denominator-Errors for how to modify your code to create NULLs instead if that is what you intend.
NULLs can be created in a new field definition directly using the reference NULL (or null lower case) in your code. This might often be done in CASE/WHEN statements to create NULL values based on a condition. An example is below. This will replace any values that are less than zero for the variable with a NULL value instead.
- CASE WHEN [integer variable] < 0 THEN NULL ELSE [integer variable] END
Also see https://support.lityxiq.com/722718-Conditional-and-Null-Handling-Functions for functions to create null values of specific types. These functions are null_as_int(), null_as_decimal(), null_as_date(), null_as_datetime(), and null_as_string().
Handling of NULL Values in New Field Calculations
For many new field formulas you may use, if a NULL value is involved in the calculation, the result of the calculation will be NULL. For example, when summing two variables, if one or both has a NULL value for a record, the result of the sum operation will be NULL. This is true for most mathematical functions and operators. The same is true for most string functions and date manipulation functions. For example, taking the substring of a NULL value results in a NULL result, not the empty string. The Coalesce function is a helpful function for dealing with NULL values in new fields, allowing you more control over the results when NULLs may be an issue. See https://support.lityxiq.com/513487-Coalesce-Function for more information.
Checking for NULL Values
Finally, you can test for the existence of NULL values in a variable and handle things differently depending on the result. Functions for doing this are described in https://support.lityxiq.com/722718-Conditional-and-Null-Handling-Functions.