Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jas0012qlik
Contributor III
Contributor III

Where not exists

I'm trying to pull information from one table, while excluding anyone who exists in another table, then using it in a filter pane.

For example....

Pull all entities in table A who have never been members of a club (all members will be present in table B, but only if they were once members).

I'm trying a concatenate, but not 100% sure how to word this.

Any suggestions?

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Jana,

I used the NoConcatenate statement so my both tables wouldn't concatanate automatically as you stated.

If you have only one common field, that's ok no need for that than.

For your requirements, it would go something like this:

TableA:

Load

     ID as ID_A,

     ...

From [whatever];

TableB:

Load

     ID as ID_B,

     ...

From [whatever];

tmp:

Load

     Distinct ID_A

Resident TableA

where not exists(ID_A,ID_B); // from previously loaded TableA and TableB

View solution in original post

4 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Jana,

Im loading some simple data to make a quick example.

A:

Load * Inline

[

Id,Name

1,A

2,B

3,C

];

B:

Load * Inline

[

Name,Phone

C,1

B,2

D,3

];

NoConcatenate

data:

Load

Id,

Name as Name_p

Resident A;

drop table A;

NoConcatenate

phones:

Load

Name,

Phone

Resident B

Where not exists(Name_p,Name);

drop table B;

After this code is run, the 'phones' table will only have the 'D' name and phone on its records, since there's no equivalent in the 'data' table.

Felipe.

jas0012qlik
Contributor III
Contributor III
Author

I'm trying to follow this but am having a little trouble.

From what I've read, the NoConcatenate is used with two tables with identical field sets. The two tables I am using only have one field in common, which is the ID. I want all the IDs from Table A which are not included in Table B to be defined as a data set, such as "No Membership on Record", and then combine that field into the Table B filter that lists all the membership types. We would use this filter to not only pull all the different memberships, but also to pull anyone who never had a membership.

We are trying something similar with another filter we're using in a separate sheet.

felipedl
Partner - Specialist III
Partner - Specialist III

Hi Jana,

I used the NoConcatenate statement so my both tables wouldn't concatanate automatically as you stated.

If you have only one common field, that's ok no need for that than.

For your requirements, it would go something like this:

TableA:

Load

     ID as ID_A,

     ...

From [whatever];

TableB:

Load

     ID as ID_B,

     ...

From [whatever];

tmp:

Load

     Distinct ID_A

Resident TableA

where not exists(ID_A,ID_B); // from previously loaded TableA and TableB

jas0012qlik
Contributor III
Contributor III
Author

I haven't  had a chance to try the code above yet, but we did find a work around until we can set that up. If you choose ll of the memberships available in the filter for members, then select excluded from the IDs column in the detail, you will get all of the 'never members'.