Page tree

 

Support | BlogContact Us | 844.332.2821

 

Contents:

This documentation applies to Trifacta Wrangler. Download this free product.
Registered users of this product or Trifacta Wrangler Enterprise should login to Product Docs through the application.

The NULL function generates null values.

Basic Usage

derive value:NULL()

Output: Generates a column of null values.

derive value: IF((ISNULL(FirstName) || ISNULL(LastName)), null(), 'ok') 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 column value is ok.

Syntax

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

Transform:

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

derive value: ISMISMATCHED (State, 'State')

You can test for valid matches for Qty using the following: 

derive value: (ISVALID (Qty, 'Integer') && (Qty > 0)) as:'valid_Qty'

The first transform flags rows 4 and 6 as mismatched.

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

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

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

derive value: (ISMISSING(State) || ISMISSING(Qty)) as:'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:

derive value: ((mismatched_State == 'true') || (valid_Qty == 'false') || (missing_State_Qty == 'true')) ? NULL() : 'ok' as:'status'

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

delete row: ISNULL('status')

Results:

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

StateQtymismatched_Statevalid_Qtymissing_State_Qtystatus
CA10falsetruefalseok

 

Your Rating: Results: PatheticBadOKGoodOutstanding! 19 rates

This page has no comments.