topic Re: Concatenate load where not exists from a sql server in QlikView

Concatenate load where not exists from a sql server

ahmed_hassan — Fri, 07 Dec 2018 09:26:47 GMT

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!

Re: Concatenate load where not exists from a sql server

avkeep01 — Fri, 07 Dec 2018 09:30:54 GMT

Hi @ahmed_hassan,

Could you try:

Where Not Exists(ID,'ID')

Where Not Exists('ID',ID)

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

Re: Concatenate load where not exists from a sql server

ahmed_hassan — Fri, 07 Dec 2018 09:34:00 GMT

It didn't work 😞

Re: Concatenate load where not exists from a sql server

Gysbert_Wassenaar — Fri, 07 Dec 2018 09:35:49 GMT

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;

Re: Concatenate load where not exists from a sql server

ahmed_hassan — Fri, 07 Dec 2018 09:46:32 GMT

It worked! Thanks a lot 🙂