Test Rule
Test rule (
) return a boolean value of True or False. For any test, a True value indicates the field passed the test and a False result indicates the field failed the test. This rule type defines data quality rules and generates pass and fail statistics but does not create derived fields. The test rule provide a method for inverting the results to the pass and fail files.
) return a boolean value of True or False. For any test, a True value indicates the field passed the test and a False result indicates the field failed the test. This rule type defines data quality rules and generates pass and fail statistics but does not create derived fields. The test rule provide a method for inverting the results to the pass and fail files. The following table provides information about the available options for each rule:
Compare to Constant
Description | After comparing a field value to a constant value, it returns True or False result. |
Parameters | • Invert Test: Select this to report the opposite results in your pass or fail file. For more information, see Inverting Rule Test. • Operator: Select from one of the following comparison operators: – Equal – Greater – Greater or Equal – Lesser – Lesser or Equal – Not Equal • Constant: In this text box, type the constant value to compare. For equal or not equal you can list one or more constants separated by a “|” character. |
Supported Data Types | String, Date, Timestamp, Boolean, Numeric, Double, Float, Long, Integer |
Remarks | The constant must be of the same data type as the profiled field. The equal operator is not recommended to use on double or float field comparisons. Do not use quotation marks with string data. The following formats are supported for date and timestamp: • yyyy-mm-dd • yyyy-mm-dd hh:mm:ss (military time) Note: If source has GMT timestamp data, the constant value should end with 'Z'. |
Compare to Field
Description | After comparing a field value to another field, returns True or False result. |
Parameters | • Invert Test: Select this to report the opposite results in your pass or fail file. For more information, see Inverting Rule Test. • Operator: Select from one of the following comparison operators: – Equal – Greater – Greater or Equal – Lesser – Lesser or Equal – Not Equal • Compare Field: Select the required field to compare from the Compare Field drop-drown list. |
Supported Data Types | String, Date, Timestamp, Boolean, Numeric, Double, Float, Long, Integer |
Remarks | The two fields must be of the same data type. The equal operator is not recommended for use on double or float field comparisons. |
Is Blank
Description | If a field is blank, then returns True. Else, it returns False. |
Parameters | None |
Supported Data Types | String |
Remarks | Counts null, white space, and empty strings as blank. |
Is Duplicate
Description | If a field is duplicated, then returns True. Else, returns False. |
Parameters | None |
Supported Data Types | String, Date, Timestamp, Boolean, Numeric, Double, Float, Long, Integer |
Remarks | The results file name cannot contain the following characters: • / • \ |
Is Null
Description | If a field is null, then returns True. Else, returns False. |
Parameters | None |
Supported Data Types | String, Date, Timestamp, Boolean, Numeric, Double, Float, Long, Integer |
Remarks | None |
Is Not Blank
Description | If a field is not blank, then returns True. Else, it returns False. |
Parameters | None |
Supported Data Types | String |
Remarks | Counts null, white space, and empty strings as blank. |
Is Not Duplicate
Description | Returns true if a field is not duplicated, returns false otherwise. |
Parameters | None |
Supported Data Types | String, Date, Timestamp, Boolean, Numeric, Double, Float, Long, Integer |
Remarks | The results file name cannot contain the following characters: • / • \ • . • .. |
Is Not Null
Description | If a field is not null, then returns True. Else, returns False. |
Parameters | None |
Supported Data Types | String, Date, Timestamp, Boolean, Numeric, Double, Float, Long, Integer |
Remarks | None |
Matches Regex
Description | Returns true if a field matches a regular expression, returns false otherwise. |
Parameters | • Invert Test: Select this to report the opposite results in your pass or fail file. • Regular Expression: Specify your own regular expression or select from the following list: – AlphaNumeric Strings-->^[a-zA-Z0-9]+$ – AnyString-->.* – ASCII characters only-->^([a-zA-Z0-9!\"#$%&',/:;<=>@_`|~ \\(\\)\\*\\+\\-\\.\\?\\[\\]\\\\\\^\\{\\}])*$") – Canada Postal Code-->^([A-Za-z]\d[A-Za-z][\s-]?\d[A-Zaz]\d) – Credit Card Number-->[1-9][0-9]{3} [0-9]{4} [0-9]{4} [0-9]{4} – Date - mm/dd/yyyy format-->^(3[0-1]|2[0-9]|1[0-9]|0[1-9])[\s{1}|\/|-](Jan|JAN|Feb|FEB|Mar|MAR|Apr|APR|May|MAY|Jun|JUN|Jul|JUL|Aug|AUG|Sep|SEP|Oct|OCT|Nov|NOV|Dec|DEC)[\s{1}|\/|-]\d{4}$ – Date - YYMMDD format-->^((\d{2}((0[13578]|1[02])(0[1-9]|[12]\d|3[01])|(0[13456789]|1[012])(0[1-9] |[12]\d|30)|02(0[1-9]|1\d|2[0-8])))|([02468][048]|[13579][26])0229)$ – Days of Week-->^(Sun|Mon|(T(ues|hurs))|Fri)(day|\.)?$|Wed(\.|nesday)?$|Sat(\.|urday)?$|T ((ue?)|(hu?r?))\.?$ – Digits Only-->\d+ – Email Address-->^(([A-Za-z0-9]+_+)|([A-Za-z0-9]+\\-+)|([A-Za-z0-9]+\\.+)|([A-Za-z0-9]+\\++))*[A-Za-z0-9]+@((\\w+\\-+)|(\\w+\\.))*\\w{1,63}\\.[a-zAZ]{2,6}$ – ISBN_10 Number-->ISBN\x20(?=.{13}$)\d{1,5}([-])\d{1,7}\1\d{1,6}\1(\d|X)$ – Male or Female-->M|F – Mr or Mrs-->Mr|Mrs – Printable Characters-->^([a-zA-Z0-9!@#$%^&amp;*()-_=+;:'&quot;|~`&lt;&gt;?/{}] {1,5})$ – Single Alphabetic character-->^[a-zA-Z]$ – Social Security Number-->^[0-9]{3}-?[0-9]{2}-?[0-9]{4}$ – Time - hh:mm AMPM format-->^ *(1[0-2]|[1-9]):[0-5][0-9]*(a|p|A|P)(m|M) *$ – Time - hh:mm:ss format-->(([0-1][0-9])|([2][0-3])):([0-5][0-9]):([0-5][0-9]) – UK Postal Code-->^([A-Z]{1,2}[0-9]{1,2}|[A-Z]{3}|[A-Z]{1,2}[0-9][A-Z])( |-)[0-9][A-Z]{2} – US Currency-->^\$?( )*\d*(.\d{1,2})?$ – US Phone Number-->^[01]?[- .]?\(?[2-9]\d{2}\)?[- .]?\d{3}[- .]?\d{4}$ – US Zipcode or Zip+4-->[0-9]{5}(-[0-9]{4})? – Vehicle Identification Number (VIN)-->^(([a-h,A-H,j-n,J-N,p-z,P-Z,0-9]{9})([a-h,A-H,j-n,J-N,p,P,r-t,R-T,v-z,V-Z,0-9])([a -h,A-H,j-n,J-N,p-z,P-Z,0-9])(\d{6}))$ AnyString is the default regular expression. For a description of Regular Expression options, see Remarks. |
Supported Data Types | String |
Remarks | Regex pattern rule is displayed in the Parameters column on the Profile tab and the Description column is displayed on the Result summary. Actian DataConnect stores and uses regular expressions and you can edit the expressions if required. Description of Regular Expressions: • AlphaNumeric - Matches strings that contain character and/or numeric data. • AnyString - Matches all strings. • ASCII characters only Postal- Matches ASCII characters only postal. • Canada Postal Code - Matches postal code for Canada. • Credit Card Number - Matches a credit card number. • Date - mm/dd/yyyy format - Flexible date validator that matches most date formats. • Date_YYMMDD - Date expression validator with format YYMMDD. • Days of Week - Matches days of the week or their abbreviations. • DigitsOnly - Expression for matching one or more digits. • EmailAddress - Matches an email address. • ISBN_10 Number - Matches format of an ISBN number. • Male Or Female - Matches M or F character. • Mr or Mrs - Matches Mr or Mrs characters. • Printable Characters - Matches all printable characters. • Single Alphabetic character - Matches a single alphabetic Character. • Social Security Number - Matches U.S. social security number. Dashes (-) are optional. • Time - hh:mm AMPM format - Matches time format hh/mm AM. • Time - hh:mm:ss format - Matches time format hh:mm:ss. • US Zipcode or Zip+4 - Matches a US zip code. • Vehicle Identification Number (VIN) - Matches US Vehicle Identification Numbers (VINs). |
Last modified date: 02/09/2024