Page tree

Release 5.0.1




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

  • For a tested value, this function returns true or false
  • Inputs can be literal values or column references.

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

NOTE: This function is similar to the ISVALID function, which tests for validity against a specified data type. However, unlike the ISVALID function, the ISMISMATCHED function also matches against missing values. See VALID Function.

Basic Usage

Column reference example:

delete row:(ISMISMATCHED(Qty, 'Integer') || (Qty < 0))

Output: Deletes any row in which the value in the Qty column does not contain a valid Integer and the value is less than zero. 

Numeric literal example:

derive type:single value: ISMISMATCHED('ZZ', 'State')

Output: Generates a new column containing true, since the value ZZ is not a valid U.S. State code.

Syntax and Arguments

derive type:single value:ISMISMATCHED(column_string,datatype_literal)

ArgumentRequired?Data TypeDescription
column_stringYstringName of column or string literal to be applied to the function
datatype_literalYstringString literal that identifies the data type against which to validate the source values

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


Name of the column or string literal to be evaluated for mismatches against the specified type.

  • Missing literals or column values generate missing string results.
    • Constants must be quoted ('Hello, World').
  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString literal or column referencemyColumn


Literal value for data type to which to validate the source column or string.

  • Column references are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString literal'Integer'

Valid data type strings:

When referencing a data type within a transform, you can use the following strings to identify each type:

NOTE: In Wrangle transforms, these values are case-sensitive.

Data TypeString
Social Security Number'SSN'
Phone Number'Phone'
Email Address'Emailaddress'
Credit Card'Creditcard'
IP Address'Ipaddress'
HTTP Code'Httpcodes'
Zip Code'Zipcode'
Date / Time'Datetime'

For custom types, you should reference the name of the type in the string value. For more information, see Create Custom Data Types.


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.
  • 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.


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



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

derive type:single value: ISMISMATCHED (State, 'State')

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

derive type:single 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 type:single 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:

ID falsefalsetrue

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

derive type:single 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')


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


See Also for ISMISMATCHED Function:


This page has no comments.