Page tree

Trifacta Dataprep


Contents:

 

The NULL function generates null values.

Wrangle vs. SQL: This function is part of Wrangle , a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

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

Tip: For additional examples, see Common Tasks.

Example - Type check functions

This example illustrates how various type checking functions can be applied to your data.

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
Parameter: Formula type Single row formula
Parameter: 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
Parameter: Formula type Single row formula
Parameter: Formula (ISVALID (Qty, 'Integer') && (Qty > 0))
Parameter: New column name '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
Parameter: Formula type Single row formula
Parameter: Formula (ISMISSING(State) || ISMISSING(Qty))
Parameter: New column name '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
Parameter: Formula type Single row formula
Parameter: Formula ((mismatched_State == 'true') || (valid_Qty == 'false') || (missing_State_Qty == 'true')) ? NULL() : 'ok'
Parameter: New column name 'status'

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

Transformation Name Filter rows
Parameter: Condition Custom formula
Parameter: Type of formula Custom single
Parameter: Condition ISNULL('status')
Parameter: Action Delete matching rows

Results:

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

StateQtymismatched_Statevalid_Qtymissing_State_Qtystatus
CA10falsetruefalseok

 

This page has no comments.