Where Not Exists

[farnsworth]

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)

;

[maxgro]

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

[pamaxeed]

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

[ramoncova06 · Accepted solution]

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;

[farnsworth]

Thank you @Ramon,  this worked.