Defining data validation rules

Qlik Compose enables you to define data validation rules that are applied to the data before it is loaded into the data warehouse. In addition to defining rules, you can also define what action should be taken when data is rejected/accepted by Compose.

To add a rule:

1. Click the Manage button at the bottom of the Data Warehouse panel.
2. In the Manage Data Warehouse Tasks window, click the link in the Mapping column for the table you want to profile.

3. In the Edit Mappings - <Name> window, click the Data Quality toolbar button. The Data Quality Rules - <Table Name> window opens.

   The default rule rejects primary keys that have a null value and reports the rows.

4. To add a new rule, click the New toolbar button. A row is added to the rules table.

5. In the Name column, specify a name for the rule.
6. Hover the mouse-cursor over the Rule column and then click the fx button that appears on the right.

7. In the Edit Data Quality Rule window create a rule using an expression. For more information on creating expressions, see Opening the expression builder.

   See also Simple Example Rule below.

8. From the drop-down list in the Error Action column, select one of the following actions (performed when the data does not meet the rule conditions):

   • Reject and report - Reject the data and send a report
   • Reject silently - Reject the data without sending a report
   • Reject and abort - Reject the data and abort the data warehouse task
   • Accept and report - Accept the data and send a report
   Information note

   • When the "report" option is selected, the row is reported to the <landing_table_name>__ex table in the data warehouse error mart.
   • When there are multiple data validation rules, Compose will stop evaluating the data after the first error (and report only that error). Once the error is fixed, additional data evaluation errors may be reported for the remaining rules, each time the data is loaded.
9. In the Description column, enter a description for the rule.

10. In the Enabled column, select or clear the check box to enable (the default) or disable the rule respectively.

    Information note

    A rule that is defined to reject or accept a non-null value (e.g. 2) in a given column will also reject/accept NULL values that appear in the same column, but in different records. To prevent this from happening, add the following condition to the rule: "and column value is not null"

    Example: LEN(${CName})<2 and (${CName} is not null)

Simple Example Rule

The following rule stipulates that the number of units in stock must be greater than 1.

${UnitsInStock}>1