Creating a Custom Data Validation Rule¶

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:
2. Open your project in Timextender
3. Navigate to the table containing the email addresses

4. Right-click the table and select Add Field to create a new field. Name the field "IsValidFormat" and select data type Boolean

   

   

5. 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