topic Data Comparison between two tables in QlikView

Data Comparison between two tables

Thu, 14 Apr 2011 17:18:35 GMT

Hi All,

I have two tables A and B and I have to comapre A.Column1 with B.column1 and have to find out what are the nonmatching data in table A.Column1.Below is an example and expected result is 400 and 500. Can you please let me know what would be a best way to do so?

A B
----- --------
100 100
200 200
300 300
400 800
500 900

Thanks,Pooja

Data Comparison between two tables

matt_crowther — Thu, 14 Apr 2011 17:40:52 GMT

There are various methods to achieve this: the simplest may well be to use the exists() function when loading table B. Alternatively you could use the below style:

Compare:
LOAD * INLINE [
F1, TableA
100, A
200, A
400, A
500, A
800, A
900, A
];

Outer Join (Compare)
LOAD * INLINE [
F1, TableB
100, B
200, B
400, B
500, B
600, B
700, B
];

This will give you the following table allowing you to compare to your hearts content:

Hope that helps - if not I'm sure there are many more methods to try.

Matt - Visual Analytics Ltd

Data Comparison between two tables

fosuzuki — Thu, 14 Apr 2011 17:47:35 GMT

Hi Pooja,

I think this can be solved using the 'NOT EXISTS' clause.

Example:


data:
load *
resident A
where not exists (A.Column1, B.Column1);

Hope this helps you.

Regards from Brazil.

Fernando

Data Comparison between two tables

Thu, 14 Apr 2011 19:45:32 GMT

Hi,

Thanks for your response.I tried this but got the error msg(field not found).It couldn't recognise B.column1 .

Data Comparison between two tables

Fri, 15 Apr 2011 12:56:03 GMT

change position of the column name in where clause