##### Page tree

You are viewing an old version of this page. View the current version.

Version 3

The `NULL` function generates null values.

## Basic Usage

null()

Output: Returns a null value.

if((isnull(FirstName) || isnull(LastName)), null(), 'not null') as:'status'

Output: If there are null values in either the `FirstName` or `LastName` column, generate a null value in the `status` column. Otherwise, the returned value is `not null`.

## Syntax and Arguments

There are no arguments for this function.

## Examples

### Example - Type check functions

This example illustrates how various type checking functions can be applied to your data.
• `ISVALID` - Returns `true` if the input matches the specified data type. See VALID Function.
• `ISMISMATCHED` - Returns `true` if the input does not match the specified data type. See ISMISMATCHED Function.
• `ISMISSING `- Returns `true` if the input value is missing. See ISMISSING Function.
• `ISNULL` - Returns `true` if the input value is null. See ISNULL Function.
• `NULL` - Generates a null value. See NULL Function.

Source:

Some source values that should match the State and Integer data types:

StateQty
CA10
OR-10
WA2.5
ZZ15
ID
4

Transformation:

Invalid State values: You can test for invalid values for State using the following:

Transformation Name `New formula` `Single row formula` `ISMISMATCHED (State, 'State')`

The above transform flags rows 4 and 6 as mismatched.

NOTE: A missing value is not valid for a type, including String type.

Invalid Integer values: You can test for valid matches for Qty using the following:

Transformation Name `New formula` `Single row formula` `(ISVALID (Qty, 'Integer') && (Qty > 0))` `'valid_Qty'`

The above transform flags as valid all rows where the `Qty` column is a valid integer that is greater than zero.

Missing values: The following transform tests for the presence of missing values in either column:

Transformation Name `New formula` `Single row formula` `(ISMISSING(State) || ISMISSING(Qty))` `'missing_State_Qty'`

After re-organizing the columns using the `move` transform, the dataset should now look like the following:

StateQtymismatched_Statevalid_Qtymissing_State_Qty
CA10falsetruefalse
OR-10falsefalsefalse
WA2.5falsefalsefalse
ZZ15truetruefalse
ID falsefalsetrue
4falsetruetrue

Since the data does not contain null values, the following transform generates null values based on the preceding criteria:

Transformation Name `New formula` `Single row formula` `((mismatched_State == 'true') || (valid_Qty == 'false') || (missing_State_Qty == 'true')) ? NULL() : 'ok'` `'status'`

You can then use the `ISNULL` check to remove the rows that fail the above test:

Transformation Name `Filter rows` `Custom formula` `Custom single` `ISNULL('status')` `Delete matching rows`

Results:

Based on the above tests, the output dataset contains one row:

StateQtymismatched_Statevalid_Qtymissing_State_Qtystatus
CA10falsetruefalseok

• Page:
• Page:
• Page:
• Page:
• Page:
• Page:
• Page:
• Page:
• Page:
• Page:
• Page:
• Page:

• No labels