Count where ID's exist in one table but not another

[#1]

Hi,

I have 2 tables loaded up in Qlikview

Client  -  This contains ALL clients

Communication - This contains all clients who have an email address

The one common field between the tables is ClientID.

Is there any way that I can use COUNT and Set Analysis to count the number of Client IDs that Exist in the Client table but DO NOT exist in the Communication table?

Thanks

Stu

[datanibbler]

Hi,

just

- LOAD those two tables like you did before

- LOAD a small table RESIDENT [All] with a WHERE NOT EXISTS([email_adress_table_ID])

- Join that to the table where you want that flag via the client_ID

HTH

Best regards,

DataNibbler

[perumal_41]

Hi Stuart,

PFA

hope it helps

[JonnyPoole · Accepted solution]

This should work, still tinkering to put it into 1 SET statement

=count( distinct ClientID) - count( DISTINCT  {$<Email={"*"}>} ClientID)

[#5]

works perfect - thanks

[#6]

Thanks - I can see this would be of use also

[#7]

Thanks  - i might use this for a later iteration