Where Not Exists Problem

[haymarketpaul]

I have the following script that attempts to concatenate one lot of data to another but omit certain records if they already exist based on a Where not exists line (see red line below).   However none of the New records get concatenated to the first table?

Wondering where i am going wrong here?

Linda_temp2:

LOAD

    UserID,

    Comp_ID,

    [Matched to GMC Database],

    [GMC Surname match Online],

    [GMC Surname match Print],

    GMC#,

    [Email GPOnline],

    [Firstname GPOnline],

    [Lastname GPOnline],

    [Email GPPrint GMCJoin],

    [Firstname GPPrint GMCJoin],

    [Lastname GPPrint GMCJoin],

    [GP Print],

    [GP Online],

    [GP Print GMCJoin],

    [Linda Source]

Resident Linda_temp;

DROP Table Linda_temp;

New_Recs:

LOAD

    UserID,

    [Email GPOnline],

    [Firstname GPOnline],

    [Lastname GPOnline],

    [GMC#],

    [Linda Source],

    [Exists?]

;

SQL SELECT DISTINCT

    u.userid            as UserID,

    userEmail            as [Email GPOnline],

    userFirstName        as [Firstname GPOnline],

    userLastName        as [Lastname GPOnline],

    ref_num                as [GMC#],

    'New Online'        as [Linda Source],

    'NO'                  as [Exists?]           

from  tbl_Users as u

inner join u.f_sub_site as ss ON u.userId = ss.userID

inner join u.f_x_sub_type_sub_site as xss ON ss.f_sub_site_id = xss.f_sub_site_id

inner join u.d_x_sub_type as xst ON xss.d_x_sub_type_id = xst.d_x_sub_type_id

where

    ss.magjournalcode IN ('HGP','GP','MM2')

and

    xst.d_x_sub_type_id = 1

and

    (ss.created_on >= '2014-03-01';

Concatenate (Linda_temp2)

//New records

LOAD

    UserID,

    [Email GPOnline],

    [Firstname GPOnline],

    [Lastname GPOnline],

    [GMC#],

    [Linda Source],

    [Exists?]

Resident New_Recs

Where not exists(UserID,UserID) or not exists(GMC#,GMC#) or not exists([Email GPOnline],[Email GPOnline]);

DROP Table New_Recs;

[Gysbert_Wassenaar]

The problem is that after you load your New_Recs table all the UserID, GMC# and Email GPOnline values exist in the in-memory database. So NOT EXISTS can't find any values that don't exist. But you should be able to concatenate the new records directly:

Linda_temp2:

LOAD

    UserID,

    Comp_ID,

    [Matched to GMC Database],

    [GMC Surname match Online],

    [GMC Surname match Print],

    GMC#,

    [Email GPOnline],

    [Firstname GPOnline],

    [Lastname GPOnline],

    [Email GPPrint GMCJoin],

    [Firstname GPPrint GMCJoin],

    [Lastname GPPrint GMCJoin],

    [GP Print],

    [GP Online],

    [GP Print GMCJoin],

    [Linda Source]

Resident Linda_temp;

DROP Table Linda_temp;

Concatenate (Linda_temp2)

LOAD

    UserID,

    [Email GPOnline],

    [Firstname GPOnline],

    [Lastname GPOnline],

    [GMC#],

    [Linda Source],

    [Exists?]

;

SQL SELECT DISTINCT

    u.userid            as UserID,

    userEmail            as [Email GPOnline],

    userFirstName        as [Firstname GPOnline],

    userLastName        as [Lastname GPOnline],

    ref_num                as [GMC#],

    'New Online'        as [Linda Source],

    'NO'                  as [Exists?]          

from  tbl_Users as u

inner join u.f_sub_site as ss ON u.userId = ss.userID

inner join u.f_x_sub_type_sub_site as xss ON ss.f_sub_site_id = xss.f_sub_site_id

inner join u.d_x_sub_type as xst ON xss.d_x_sub_type_id = xst.d_x_sub_type_id

where

    ss.magjournalcode IN ('HGP','GP','MM2')

and

    xst.d_x_sub_type_id = 1

and

    (ss.created_on >= '2014-03-01';

[sunny_talwar · Accepted solution]

Since UserID is the same field name in the above two tables before you use it, QV creates a concatenate list to check against, which includes UserID from both the tables and it won't even find anything which doesn't match. Same is true for the other two.

Try it like this:

Linda_temp2:

LOAD

    UserID,

    Comp_ID,

    [Matched to GMC Database],

    [GMC Surname match Online],

    [GMC Surname match Print],

    GMC#,

    [Email GPOnline],

    [Firstname GPOnline],

    [Lastname GPOnline],

    [Email GPPrint GMCJoin],

    [Firstname GPPrint GMCJoin],

    [Lastname GPPrint GMCJoin],

    [GP Print],

    [GP Online],

    [GP Print GMCJoin],

    [Linda Source]

Resident Linda_temp;

DROP Table Linda_temp;

New_Recs:

LOAD

   UserID as UserID1,

    [Email GPOnline] as [Email GPOnline1],

    [Firstname GPOnline],

    [Lastname GPOnline],

    [GMC#] as [GMC#1],

    [Linda Source],

    [Exists?]

;

SQL SELECT DISTINCT

    u.userid            as UserID,

    userEmail            as [Email GPOnline],

    userFirstName        as [Firstname GPOnline],

    userLastName        as [Lastname GPOnline],

    ref_num                as [GMC#],

    'New Online'        as [Linda Source],

    'NO'                  as [Exists?]        

from  tbl_Users as u

inner join u.f_sub_site as ss ON u.userId = ss.userID

inner join u.f_x_sub_type_sub_site as xss ON ss.f_sub_site_id = xss.f_sub_site_id

inner join u.d_x_sub_type as xst ON xss.d_x_sub_type_id = xst.d_x_sub_type_id

where

    ss.magjournalcode IN ('HGP','GP','MM2')

and

    xst.d_x_sub_type_id = 1

and

    (ss.created_on >= '2014-03-01';

Concatenate (Linda_temp2)

//New records

LOAD

UserID1 as UserID,

    [Email GPOnline1] as [Email GPOnline],

    [Firstname GPOnline],

    [Lastname GPOnline],

    [GMC#1] as [GMC#],

    [Linda Source],

    [Exists?]

Resident New_Recs

Where not exists(UserID,UserID1) or not exists(GMC#,GMC#1) or not exists([Email GPOnline],[Email GPOnline1]);

DROP Table New_Recs;

Highlighted the changes in red.

HTH

Best,

Sunny

[haymarketpaul]

Perfect - Thank you

[sunny_talwar]

No problem

I am glad we were able to help.

Best,

Sunny