Skip to main content

ISMISSING Function

The ISMISSING function tests whether a column of values is missing or null. For input column references, this function returns true or false.

  • You can define a conditional test in a single step for valid values. See IFMISSING Function.

  • Missing values are different from null values. To test for the presence of null values exclusively, see ISNULL Function.

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

ismissing(Qty)

Output: Returns true if the value in the Qty column is missing.

Syntax and Arguments

ismissing(column_string)

Argument

Required?

Data Type

Description

column_string

Y

string

Name of column or string literal to be applied to the function

For more information on syntax standards, see Language Documentation Syntax Notes.

column_string

Name of the column or string literal to be tested for missing values.

  • Missing literals or column values generate missing string results.

  • Multiple columns are supported.

  • Wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

String literal or column reference

myColumn

Examples

ヒント

For additional examples, see Common Tasks.

Example - Type check functions

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

Functions:

Item

Description

VALID Function

Tests whether a set of values is valid for a specified data type and is not a null value.

ISMISMATCHED Function

Tests whether a set of values is not valid for a specified data type.

ISMISSING Function

The ISMISSING function tests whether a column of values is missing or null. For input column references, this function returns true or false.

ISNULL Function

The ISNULL function tests whether a column of values contains null values. For input column references, this function returns true or false.

NULL Function

The NULL function generates null values.

Source:

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

State

Qty

CA

10

OR

-10

WA

2.5

ZZ

15

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.

注記

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 theQtycolumn 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:

State

Qty

mismatched_State

valid_Qty

missing_State_Qty

CA

10

false

true

false

OR

-10

false

false

false

WA

2.5

false

false

false

ZZ

15

true

true

false

ID

false

false

true

4

false

true

true

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:

State

Qty

mismatched_State

valid_Qty

missing_State_Qty

status

CA

10

false

true

false

ok