User Guide > Designing and Executing Data Profile > Rule and Parameter Reference > Test Rule
Was this helpful?
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.
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 Invert 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 Invert 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
In Range
 
Description
If the value is within specified range, then returns True. Else, returns False.
Parameters
Lower Bound: The lower range value. Select Inclusive to include the lower bound number in the range.
Upper Bound: The upper range value. Select Inclusive to include the upper bound number in the range.
Supported Data Types
Date, Timestamp, Numeric, Double, Float, Long, Integer
Remarks
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 lower bound and upper bound values should end with 'Z'.
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;amp;*()-_=+;:'&amp;quot;|~`&amp;lt;&amp;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: 07/26/2024