How to Implement Data Quality Rules

    It's a fact of life that the Qlik applications we build will have data quality issues. After all, we are humans! The good news is that there is a simple solution using the evaluate function that can help us to build up a list of data quality rules to check against our data.

     

    The first step is build up the rules in a spreadsheet along with a formula that will evaluate to true if the record is in error. Refer to DataQualityRules.xlsx below. I've put two basic examples but any expression that will evaluate against a single row of data should work. For example, you could use len to check for correct string length, or match to check for particular values.

    Data Quality Rules 1.PNG

     

    The second step is to the load our sample data set and ensure we have a key field that uniquely identifies each record in our data. In the example the field is called RowNo but can be any unique key field in the data.

     

    The last step is to evaluate each record of our data set against the data quality rule using a for loop. The result is a table that can be used on a Data Quality tab, for example, to highlight problems with the data. Click on each rule will allow you to see which records broke each rule.

     

    Data Quality Rules 2.PNG

    Note: This solution will work for both QlikView and Qlik Sense.

     

    Matt Nunn

    Qlik Consulting