topic Re: Where Not Exists Problem in QlikView

Where Not Exists Problem

haymarketpaul — Fri, 15 May 2015 12:00:12 GMT

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;

Re: Where Not Exists Problem

Gysbert_Wassenaar — Fri, 15 May 2015 12:06:43 GMT

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';

Re: Where Not Exists Problem

sunny_talwar — Fri, 15 May 2015 12:08:29 GMT

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

Re: Where Not Exists Problem

haymarketpaul — Fri, 15 May 2015 13:15:43 GMT

Perfect - Thank you

Re: Where Not Exists Problem

sunny_talwar — Fri, 15 May 2015 13:17:44 GMT

No problem

I am glad we were able to help.

Best,

Sunny