VALID Function

Tests whether a set of values is valid for a specified data type and is not a null value.
  • For a specified data type and set of values, this function returns true or false.
  • Inputs can be literal values or column references.

You can use the ISVALID function keywords interchangeably.

  • You can define a conditional test in a single step for valid values. See IFVALID Function.
  • This function is similar to the ISMISMATCHED function, which tests for mismatches against a specified data type. However, the ISMISMATCHED function also matches against missing values, while the ISVALID function does not. See ISMISMATCHED Function.

Basic Usage

Column reference example:

keep row:(ISVALID(Qty, 'Integer') && (Qty > 0))

Output: Keeps any row in which the value in the Qty column contains a valid Integer and the value is greater than zero.

Numeric literal example:

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

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

Syntax

derive type:single value:ISVALID(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.

column_string

Name of the column or string literal to be evaluated for validity.

  • 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

datatype_literal

Literal value for data type to which to match the source column or string. For more information, see Valid Data Type Strings.

  • 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
String'String'
Integer'Integer'
Decimal'Float'
Boolean'Bool'
Social Security Number'SSN'
Phone Number'Phone'
Email Address'Emailaddress'
Credit Card'Creditcard'
Gender'Gender'
Object'Map'
Array'Array'
IP Address'Ipaddress'
URL'Url'
HTTP Code'Httpcodes'
Zip Code'Zipcode'
State'State'
Date / Time'Datetime'

Examples

Example - Type check functions

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

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

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:

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

Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation
Need help? Visit our support page.