NULL Values

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.

 

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().

 

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.