topic Comparing data between two identical tables from different data sources in QlikView

Comparing data between two identical tables from different data sources

Thu, 29 Apr 2010 23:13:10 GMT

Hi guys. I'm doing a data migration effort between two databases and I wanted to use QlikView to do some QA. Basically, I have a data table in one database that I'm migrating to another, so the data should be exactly the same, except that the columns are named differently. Is there a good way to compare two tables in the QlikView script to find data discrepancies? There will be a unique key identifier that will persist between the two DB's, so I can key off of that. I basically want the script to spit out rows that have differences. Any ideas? Thanks!

Eric

Comparing data between two identical tables from different data sources

vgutkovsky — Fri, 30 Apr 2010 01:40:40 GMT

I would read in both tables, make a straight table with the key as the dimension, and the expression would be something like if(Field1 <> Field2,1,0). Then sort by expression descending. This should allow you to see any discrepancies. You could also add the fields as dimensions to see the different values.

Regards,

Comparing data between two identical tables from different data sources

johnw — Fri, 30 Apr 2010 20:38:38 GMT

Attached is one approach. I concatenate the tables into one table, adding a "Table" field and a "Data" field. The "Data" field contains everything you want to compare, typically everything but the key, but there may be other things you want to skip. Then I inner join the concatenated table to a list of key fields where the data is different or one or the other records is missing. That leaves me with a list of only records where there are differences. To highlight the differences, I use a background color expression. If the row is missing from one of the tables, I highlight the key field and the table. Otherwise, I highlight any field where there are two values for the key instead of just one.

Comparing data between two identical tables from different data sources

qw_johan — Mon, 31 Jan 2011 23:29:56 GMT

Hi,

I am very interested in how you highlight the differences.
You say you use a background color expression. I have been looking at the file but didn't find any code about that.

Could you please explain it to me....

Thanks

Comparing data between two identical tables from different data sources

johnw — Tue, 01 Feb 2011 19:06:26 GMT

Next to each dimension is a + sign. Click on it to bring up some formatting options for that dimension. One of those options is "Background Color". Click on it to select it and then hit "Edit" to see the expression.

Comparing data between two identical tables from different data sources

qw_johan — Tue, 01 Feb 2011 21:02:58 GMT

Thanks for your quick reply.
I didn't know that I could addan expression there.

Thanks