Concatenate load where not exists from a sql server

[ahmed_hassan]

Dear all,

I am trying to concatenate a QVD table with an SQL table. However,  where not exists is not working.

Source:
LOAD ID, 
     Price, 
     Status, 
     Date
FROM
[..\QVDs\Source.qvd]
(qvd);

Concatenate(Source)
LOAD ID, 
     Price, 
     Status, 
     Date,
Where Not Exists (ID);

SQL SELECT *
FROM SAP.dbo.SQL;


STORE Source into [$(FolderExtract)Source.qvd] (qvd);



drop Table Source;

I have tried to use "Where Not Exists(ID)" in different places, but I get 0 data from the SQL server.

Just for info:I tested "where WildMatch(Status,'*Open*')" and everything worked fine.

Thanks in advance!

[avkeep01]

Hi @ahmed_hassan,

Could you try: 

Where Not Exists(ID,'ID')

OR 

Where Not Exists('ID',ID)

The text between the qoutes defines the field from the SQL source. 

[ahmed_hassan]

It didn't work 😞

[Gysbert_Wassenaar · Accepted solution]

What happens is that every ID is pulled from the SQL server table. Then each record from the SQL server table is pushed upwards to be processed in the preceding LOAD with the where exists clause. But by the time that where exists is applied the ID is already fetched from the SQL server table. What I think happens is that that means the ID value already exists by then and the where exists clause will discard that record.

You can solve that by doing things a little different:

Source:
LOAD ID, 
    ID as ID_Lookup,
     Price, 
     Status, 
     Date
FROM
[..\QVDs\Source.qvd]
(qvd);

Concatenate(Source)
LOAD ID,
     Price, 
     Status, 
     Date,
Where Not Exists (ID_Lookup, ID);

DROP FIELD ID_Lookup
SQL SELECT *
FROM SAP.dbo.SQL;

[ahmed_hassan]

It worked! Thanks a lot 🙂