Table Comparison app: quality control and changes
You might ask why do you need this kind of tool?
I came up with this idea during one of the projects when we quickly created an application and applied a number of data transformations on the Qlik level to satisfy business needs. One of the tables was the Equipment DIM table were 26 different tables from the Data Warehouse (DWH) combined with additional business rules. As we all know, Qlik can do everything, or close to it. However, it’s not always the best tool for a job. In this case, the business requirement was to enable the re-use of applied business logic by other applications and even by different BI tools in the future. That’s why I suggested migrating that logic into the DWH and build an artefact (table, view etc.).
The next question is: how you know that the logic in DWH was applied correctly, and here, the Table Comparison app takes its turn.
ILLUSTRATION: TOMASZ WALENTA
Let me show you how it works!
First of all, make sure that you have stored the data from the tables into QVD-files.
The assumption is that both of your QVD-files have the same field names for the columns with the same data.
SetUp sheet allows you to configure the following settings:
- Path to a QVD file with original data and table name
- Path to a QVD file with a new version of the data and table name
- The name of the field two tables should be connected and compared on. This is your unique identifier for each record.
- The fields you might want to exclude from the comparison. A good example is a field with autonumbered KEY in QVD.
When you finish with the configuration, click Reload button.
I used the following sample data sets to demonstrate how it works. You can find them on the first sheet in the Load Script.
Field Names comparison is pretty simple. However, quite often, the devil is in details. That’s why I created another sheet that helps you to see if there are any missing records or if values in two datasets are different for the same record. You can find this information on the ‘Values Comparison’ sheet.
First of all, it shows you a list of inconsistencies found:
So, this tool just helped us to see that there are several problems in this data set:
There are two not matching fields (one in Old Dataset and one in New Dataset) The record with the key 123 has different values in the field [Install Date]. Record 124 doesn’t exist in a new dataset, and record 126 is missing in Old one. There might be many reasons why the datasets do not match exactly. However, it’s an excellent start to have a chat with your colleagues to find these reasons and resolve the issues.
You are welcome to download this app from here if you ever need to compare two versions of the same data. Enjoy!
Leave a comment if you have any questions, have an idea for improvement or want to leave your feedback.