User Tools

Site Tools


transformations:verify

VERIFY DATA IN ANOTHER TABLE

Category: Transform / Advanced


Description

This action verifies the quality of data in another table and can accumulate verification results.


Use cases

Multiple Verify actions can be chained together to create data validation pipelines that perform various checks on the same dataset.

Use this action to confirm the data integrity of a dataset before passing it downstream. Apply cleaning actions and/or branch the dataset if data verification fails.


Action settings

SettingDescription
TableSelect the table to perform verifications on.
ModeSelect the verification mode to perform. Options: General verification, Verify text values, Verify numbers, Verify dates,
Verify value content, Verify column names, Custom rules.
Append resultsCheck on to accumulate all verification results within this action's dataset.
ColumnsSelect which columns to apply the verification mode to. Options: All columns, Selected columns (and select the
desired columns), All except selected (and select the columns not to verify).


General verification options

OptionDescription
Contains no duplicatesThis verification fails if duplicate values are detected within a column.
Has non-empty or non-whitespace valuesThis verification fails if any empty or whitespace characters detected.
Contains no empty valuesThis verification fails if any empty characters are detected. Whitespace characters are not checked with this option.
Contains no errorsThis verification fails if any cells in the selected columns are returning errors, and returns the error text.
Matches regular expression*Enter a regular expression in the field to match values against. Verification fails if any values do not match the expression, and the values are returned.
Only contains/Doesn't contain any values from columnThis option requires the Verify action to reside with an action chain that produces an incoming dataset. Select a column in the incoming dataset to compare to. If Only contains is selected, this verification fails for any values that do not appear in the selected column of the incoming dataset. If Doesn't contain any is selected, the verification fails for any values that do appear in the selected column of the incoming dataset. Note: this setting is case-sensitive.
If the whole table is emptySelect whether it's acceptable that the other table is empty (It's okay), or not (It's NOT okay).

* Setting can be specified using a parameter.


Verify text values options

OptionDescription
Contains only text valuesThis verification fails for values that are not text values.
Contains no trailing whitespaceThis verification fails for text values containing trailing spaces/whitespace.
Contains no leading whitespaceThis verification fails for text values containing leading spaces/whitespace.
Isn't whitespaceThis verifcation fails cells containing only spaces/whitespace.
Contains no linebreaksThis verification fails for text values containing linebreaks.
Contains no system (hidden) charactersThis verification fails for text values containing system (hidden) characters.
Isn't in Uppercase/LowercaseIf Uppercase is selected, verification fails for values containing any capital letters. If Lowercase is selected, verification fails for values containing any lowercase letters.
Text length is less than*Select a number to represent the text length. This verification fails if the text length is greater than or equal to this value.
Text length is greater than*Select a number to represent the text length. This verification fails if the text length is less than or equal to this value.
Only contains/Doesn't contain…*Enter characters to match against. If Only contains is selected, verification fails if values do not contain any of the characters. If Doesn't contain is selected, verification fails if values contain any of the characters. Note: This action is case insensitive.
Has at least…*Enter the minimum number of words tested for. Verification fails if cell values have fewer than the number of words entered.
Has at most…*Enter the maximum number of words tested for. Verification fails if cell values have more than the number of words entered.
ExceptionsOptions: Allow empty values and Allow error values. Check either of these on to ignore empty and/or error values.

* Setting can be specified using a parameter.


Verify numbers options

OptionDescription
Contains only numbersThis verification fails for values that are non-numeric.
Is not (specific number)*This verification fails for values equal to the number entered.
Is (integer/fractional)If Integer is selected, verification fails for all values with a fractional component. If Fractional is selected, verification fails for all whole number values.
Is (greater/equals)* Enter a number to test against. If Greater is selected, verification fails for values less than this number. If Greater or equals is selected, verification fails for values less than or equal to the number.
Is (less/equals)*Enter a number to test against. If Less is selected, verification fails for values greater than this number. If Less or equals is selected, verification fails for values greater than or equal to the number.
ExceptionsOptions: Allow empty values and Allow error values. Check either of these on to ignore empty and/or error values.

* Setting can be specified using a parameter.


Verify dates options

OptionDescription
Dates areSelect what format dates appear in. Options: Number dates or Text dates formatted as (and enter a valid date format).
Contains only datesThis verification fails for values that do not represent dates.
Later than (date)* Select a date to test against. This verification fails for dates that occur prior to the selected date.
Earlier than (date)*Select a date to test against. This verification fails for dates that occur after the selected date.
Has/Doesn't have the time partIf Has is selected, verification fails for values that don't include the decimal time part. If Doesn't have is selected, verification fails for values that include the date part.
Later than (days)* Select a time range (days, weeks, months, years) and enter the number of time ranges to test against. This verification fails for dates that occur prior to the number of time ranges selected.
Earlier than (days)*Select a time range (days, weeks, months, years) and enter the number of time ranges to test against. This verification fails for dates that occur after the number of time ranges selected.
ExceptionsOptions: Allow empty values and Allow error values. Check either of these on to ignore empty and/or error values.

* Setting can be specified using a parameter.


Verify value content options

OptionDescription
Content typeSelect the Content type to test for. Options: Email, JSON, XML, URL, US/Canada phone number, or GUID/UUID.
This verification fails for values that are not of the type selected.
ExceptionsOptions: Allow empty values and Allow error values. Check either of these on to ignore empty and/or error values.


Verify column names options

OptionDescription
Only contains/Doesn't contain (names from column)This option requires the Verify action to receive a dataset from the previous action. Select a column from the incoming dataset. If Only contains is selected, verification fails for column names that are not found in the selected (incoming) column. If Doesn't contain any is selected, verification fails for column names found in the selected (incoming) column.
Only contains/Doesn't contain (characters)*Enter a string of characters. If Only contains is selected, verification fails for column names that don't include all of the characters entered. If Doesn't contain any is selected, verification fails for any column names that include any of the characters entered. This action is case-sensitive.
There always exists column*Enter a column name to search for. Verification fails if the column name does not appear in the selected table. The only return value for this option appears in the "Verification rule" column.
There never exists column*Enter a column name to search for. Verification fails if the column name appears in the selected table. The only return value for this option appears in the "Verification rule" column.
All column names contain*Enter a string of characters to search for. Verification fails for column names that do not contain this string.
No column names contain*Enter a string of characters to search for. Verification fails for column names that contain this string.
Column count is less than*Enter a number of columns to verify against. Verification fails if the table contains fewer columns than the value entered. The only return value for this option appears in the "Verification rule" column.
Column count is greater than*Enter a number of columns to verify against. Verification fails if the table contains more columns than the value entered. The only return value for this option appears in the "Verification rule" column.

* Setting can be specified using a parameter.


Custom rules options

Enter one or more if…then…* statements to test values against. Use column names within the selected "Table" field to build the if tests. Enter a text value that represents the if test's description for the then argument (as this value is written into the "Verification rule" column of the action's output. * Setting can be specified using a parameter.


Remarks

For a majority of the options, you can define a Custom verdict (return Verification rule value) by clicking the blue pencil icon at the right of the option and entering the verdict text. The custom verdicts can be helpful, for instance, if you want to return validation results in other languages than English.

The "Verify data in another table" action can also be chained with the "Call with another table" action that can be used to build custom data validation rules.

transformations/verify.txt · Last modified: 2024/12/01 16:02 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki