The IFMISMATCHED function writes out a specified value if the input expression does not match the specified data type or typing array. Otherwise, it writes the source value. Input can be a literal, a column reference, or a function.

The ISMISMATCHED function simply tests if a value is mismatched. See ISMISMATCHED Function.

Basic data type:

derive type:single value:IFMISMATCHED(my_ssn,'SSN', 'XXX-XX-XXXX') as:'final_SSN'

Output: Generates a new column called, final_SSN, which contains the value XXX-XX-XXXX if the value in my_ssn does not match the SSN data type. 

Data type with formatting options:

For data types with formatting options, such as Datetime, you can specify the format using an array, as in the following:

set col: month_Date value: IFMISMATCHED(month_Date, ['Datetime','mm-dd-yy','mm*dd*yy'], null())

Output: Sets values in the month_Date column to be null if they are mismatched against Datetime values in the mm-dd-yy or mm*dd*yy formats.

derive type:single value:IFMISMATCHED(column_string, data_type_literal, computed_value)

ArgumentRequired?Data TypeDescription
source_valueYstringName of column, string literal or function to be tested
datatype_literalYstringString literal or array that identifies the data type against which to validate the source values
output_valueystringString literal value to write

source_value

Name of the column, string literal, or function to be tested for data type matches.

Required?Data TypeExample Value
YesString literal, column reference, or functionmyColumn

datatype_literal

Literal value or string array that identifies data type to which to validate the source column or string.

Required?Data TypeExample Value
YesString literal'Integer'

Valid data type strings:

output_value

The output value to write if the tested value is mismatched for the specified data type.

Required?Data TypeExample Value
YesString or numeric literal'Data type mismatch'

Example - IF* functions for data type validation