ColumnValues

Runs an expression against the values in a column.

Syntax

ColumnValues <COL_NAME> <EXPRESSION>

• COL_NAME – The name of the column that you want to evaluate the data quality rule against.

  Supported column types: Any column type

• EXPRESSION – An expression to run against the rule type response in order to produce a Boolean value. For more information, see Expressions.

Example: Allowed values

The following example rule checks whether each value in the specified column is in a set of allowed values (including null, empty, and strings with only whitespaces).

ColumnValues "Country" in [ "US", "CA", "UK", NULL, EMPTY, WHITESPACES_ONLY ]
ColumnValues "gender" in ["F", "M"] where "weightinkgs < 10"

Example: Regular expression

The following example rule checks the values in a column against a regular expression.

ColumnValues "First_Name" matches "[a-zA-Z]*"

Example: Date values

The following example rule checks the values in a date column against a date expression.

ColumnValues "Load_Date" > (now() - 3 days)

Example: Numeric values

The following example rule checks whether the column values match a certain numeric constraint.

ColumnValues "Customer_ID" between 1 and 2000

Null behavior

For all ColumnValues rules (other than != and NOT IN), NULL rows will fail the rule. If the rule fails due to a null value, the failure reason will display the following:

Value: NULL does not meet the constraint requirement!

The following example compound rule provides a way to explicitly allow for NULL values:

(ColumnValues "Age" > 21) OR (ColumnValues "Age" = NULL)

Negated ColumnValues rules using the != and not in syntax will pass for NULL rows. For example:

ColumnValues "Age" != 21

ColumnValues "Age" not in [21, 22, 23]

The following examples provide a way to explicitly fail NULL values

(ColumnValues "Age" != 21) AND (ColumnValues "Age" != NULL)

ColumnValues "Age" not in [21, 22, 23, NULL]