topic Display only the missing records in QlikView

Display only the missing records

Anonymous — Fri, 03 Feb 2012 12:01:49 GMT

Hello all

I am trying to find missing records between two tables. The first table is a SQL table and the second table is a QVD.

I am currently using the following syntax which does work.

====================================================================

Table_A:
SQL SELECT [Id] As Id
FROM dbo."Table_A";

Table_B:
LOAD Id AS Id1
FROM (qvd);

Table_C:
LOAD     Id as MissingIDs
RESIDENT     Table_A
WHERE     NOT EXISTS(Id1,Id);

====================================================================

However, i was wondering if there is a more efficient way of doing it?? i.e in SQL Server i would use the syntax below. Is there something similar in QlikView??

====================================================================

SELECT Table_A.Id

FROM Table_A LEFT OUTER JOIN Table_B ON Table_A.Id = Table_B.Id

WHERE (Table_B.Id IS NULL)

====================================================================

Any help would be greatly appreciated.

Display only the missing records

hic — Fri, 03 Feb 2012 12:08:10 GMT

If you load your two tables

Table_A:
SQL SELECT [Id] As Id,

Id as Id_A

FROM dbo."Table_A";

Table_B:

LOAD Id,

Id AS Id_B

FROM (qvd);

then you can right-click on Id_A and make a Select All. After this, you right-click on Id_B and Select Excluded.

Display only the missing records

CELAMBARASAN — Fri, 03 Feb 2012 12:29:32 GMT

Hi,

You can do it in another way as

Table_A:

SQL SELECT [Id] As Id,

Field as Field1
FROM dbo."Table_A";

Outer Join

Table_B:
LOAD Id,

Field as Field2
FROM (qvd);

Then you can get the missing id's as below

MissingIds:

LOAD

Resident

Table_A

Where Field2 <> Null();

Drop Table Table_A;

Celambarasan

Display only the missing records

Anonymous — Fri, 03 Feb 2012 13:11:00 GMT

Hi Guys

Thanks for the responses. There are obviously different ways to achieve the same result however,

Celambarasan's suggestion was the one i was looking for.

Many thanks

Robbie