The IFVALID function writes out a specified value if the input expression matches the specified data type. Otherwise, it writes the source value. Input can be a literal, a column reference, or a function.


The VALID function simply tests if a value is valid. See VALID Function.

derive type:single value:IFVALID(myZip,'ZipCode', 'ok') as:'myZipCheck'

Output: Generates a new column called, myZipCheck, which contains the value ok if the value in myZip matches the ZipCode 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: validDate value: IFVALID(myDate, ['Datetime','mm-dd-yy','mm*dd*yy'], 'true')

Output: Sets the values in the validDate column to true, if the corresponding value in the myDate column is a valid Datetime value in yy-mm-dd or yy*mm*dd format.

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

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


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


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

Required?Data TypeExample Value
YesString literal'Integer'

Valid data type strings:


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

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

Example - IF* functions for data type validation