Display only the missing records

[Anonymous]

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.

[hic]

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.

[CELAMBARASAN · Accepted solution]

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

          Id

Resident

     Table_A

Where Field2 <> Null();

Drop Table Table_A;

Celambarasan

[Anonymous]

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