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]