topic Re: Where Not Exists in QlikView

Where Not Exists

farnsworth — Fri, 20 Nov 2020 18:02:30 GMT

Hi All, I've trying to load two tables from source data outside of my control. I have two tables of call data, Table1 which contains all data which should be one row of data per call, each call has a unique id. But there is an issue where about 5% of the data has two rows per call which is not duplicate data and in this case the call has two different unique ids.

Table2 which is the "clean" version of the data where I have tidied it up to be one row per call as I need it but contains the unique id for both calls as two separate fields

So what I need to do is load all in Table1 where the data isn't in Table2 in either ID field. Then once I have removed the table2 data from Table1 i can concatenate the clean data back in.

So what I have done so far is to load the two sets of unique ids in Table2 to a table then I load Table1 where the unique id doesn't exist in the just created table. This isn't loading or removing the data correctly

This is the code:

Table2:

LOAD

UniqueIDstoRemove as %CALLKEY

FROM

CleanData.qvd

(qvd);

Table1:

LOAD

FROM

AllCalls.qvd

(qvd)

WHERE NOT EXISTS (%Call_Key,%Call_Key)

;

Re: Where Not Exists

maxgro — Wed, 30 Sep 2015 17:22:49 GMT

could you post an example with some records of table1, table2 and the expected output?

Re: Where Not Exists

pamaxeed — Wed, 30 Sep 2015 17:30:52 GMT

try:

WHERE NOT EXISTS (%CALLKEY, ID_FIELD)

where teh %CALLKEY is the previous loaded field in Table2 and the ID_FIELD is the field of the current load which u want to compare with %CALLKEY....

Cheers,

Patric

Re: Where Not Exists

ramoncova06 — Wed, 30 Sep 2015 18:37:44 GMT

because you are using the same field in your do not exist, any duplicates from table1 will be excluded

so you could do something like

This is the code:

Table2:

LOAD

UniqueIDstoRemove as %CALLKEY,

UniqueIDstoRemove as %KEYTable1

FROM

CleanData.qvd

(qvd);

Table1:

LOAD

FROM

AllCalls.qvd

(qvd)

WHERE NOT EXISTS (%KEYTable1,%Call_Key)

;

drop field %KEYTable1;

Re: Where Not Exists

farnsworth — Thu, 01 Oct 2015 09:03:11 GMT

Thank you @Ramon, this worked.