Comparing data between two identical tables from different data sources

[#1]

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

[vgutkovsky]

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,

[johnw]

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.

[qw_johan]

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

[johnw]

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.

[qw_johan]

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

Thanks