Skip to main content

Creating a Custom Data Validation Rule

  • December 16, 2024
  • 0 replies
  • 3 views

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Custom data validation rules allow you to ensure data quality by defining specific criteria for your data. This feature is particularly useful when you need to validate data that doesn't fit standard data validation rules. These custom validation rules help maintain data integrity by flagging records that don't meet specified criteria. For example, you might use a custom validation rule to ensure that a product code follows a specific format or that a date falls within an acceptable range.

Data Validation using Custom Field Transformations and Conditions

  1. Create a custom field for error flagging:
    1. Open your project in TimeXtender
    2. Navigate to the table containing the email addresses
    3. Right-click the table and select Add Field to create a new field. Name the field "IsValidFormat" and select data type Boolean
  2. Right-click the IsValidFormat field and select Field Transformations
  3. In the Field Transformations pane, select the Fixed Value operator and enter 0 as the value and click Add
  4. Repeat step 3 and enter 1 as the value and click Add
  5. Right-click Fixed value 1 and select Add Condition
  6. In the Conditions pane, click Add
  7. Add the following SQL statement, add the field name parameter and click OK
    [EmailAddress] LIKE '%@%.%'
  8. Right-click the IsValidFormat field and select Field Validations
  9. Enter 1 as the value and click Add. This means that all records with a value of 1 in this field are to be considered valid, and any other value is to be considered an error.
  10. Deploy and execute the table
  11. Navigate to Reports in the ribbon and select Errors. Select the database and the table to see a list of errors. In this case we have one error. In other words one record where IsValidFormat=False (i.e. 0) since the email address does not fit the custom validation rule we setup since it does not contain at @ symbol

 

Data Validation using Conditional Lookup Fields

Example: Filtering Junk Email Addresses

Suppose your data stewards maintain a list of invalid emails in an Excel file. By ingesting this data from the Excel file into TimeXtender, we can get a list of the Junk Email Addresses that we would like to exclude from our main table.

To validate against this list:

  1. Add a Conditional Lookup by dragging the field from the JunkAddresses onto the EmailAddress table, selecting the fields needed for the join and clicking OK. Rename the conditional lookup field JunkAddress. The conditional lookup field will show the records containing junk addresses.
  2. Then add a new boolean field similar called IsJunkAddress, by right-clicking the table and selecting Add Field.
  3. Right-click the IsJunkAddress field and add 0 and 1 as fixed value transformations. Right-click the fixed value 1 transformation and click Add condition
  4. Select the field JunkAddress and operator Is empty and click Add

     

  5. Right-click the fixed value 0 transformation and click Add condition. Select the field JunkAddress and operator Is not empty and click Add
  6. Right-click the IsJunkAddress field and select Field Validations. Enter 0 as the value and click Add. This means that all records with a value of 1 in this field are to be considered valid (i.e. the records where JunkAddress is empty, in other words is not a junk address), and any other value is to be considered an error
  7. Deploy and execute the table. Preview the errors under the Reports > Errors. These records will be excluded from the valid table

Custom Transformation Flags

Example: Expiring Old Data

Some scenarios require dynamic logic, such as expiring records based on age and other criteria. For instance:

  1. Add a custom integer field, called IsExpired
  2. Apply a custom field transformation on the new field to dynamically calculate expiration based on conditions, such as:
    CASE
      WHEN PersonType = 'A' AND CreatedDate < GETDATE() - 365 THEN 1
      ELSE 0
    END
    
  3. If we want to flag records older than a year for PersonType A then we want to exclude records where IsExpired=1, and we want to keep the valid records where IsExpired=0. Therefore we create a data validation rule similar to below

     

Did this topic help you find an answer to your question?

0 replies

Be the first to reply!

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings