topic Re: where not in but with qvd files in QlikView

where not in but with qvd files

Anonymous — Thu, 25 Sep 2014 19:56:37 GMT

Hi I am relatively new to Qlikview and coming from an SqlServer BI background. I am loading data into an document but now i need to filter out some data during the load, basically want all data from one table that is not in the other table. With sql I could do something like this:

SELECT AccountId, Email,Id

FROM Contact

where Id not in

(SELECT ContactId

FROM CampaignMember)

But now I have these two qvd files contact.qvd and campaignmember.qvd. So into the document I want to load all the data from contact that does have its ContactId in campaignmember.qvd .

Could anyone help me with this ?

Regards

Birkir

Re: where not in but with qvd files

Anonymous — Thu, 25 Sep 2014 20:02:59 GMT

look to "where not exists" in the QV help

Re: where not in but with qvd files

JonnyPoole — Thu, 25 Sep 2014 20:04:43 GMT

Use the 'exists' function. So first load the contacts from campaign members.

Then load the contacts where Id does not exist in the ContactId field from the pre-loaded campaign member table.

load

ContactId

from <Campaignmember source> ;

Load

AccountId,

Email,

from <Contactsource>

where not exists(ContactId,Id) ;

Re: where not in but with qvd files

Anonymous — Thu, 25 Sep 2014 21:52:01 GMT

Hi,

Jonathan thank you so much for your help. This worked exactly as planned.

Regards

Birkir

Re: where not in but with qvd files

JonnyPoole — Fri, 26 Sep 2014 00:04:54 GMT

Great to hear