Where not exists

[jas0012qlik]

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?

[felipedl]

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]

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 · Accepted solution]

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]

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