topic Re: Count where ID's exist in one table but not another in QlikView

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

Fri, 26 Sep 2014 14:55:31 GMT

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

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

datanibbler — Fri, 26 Sep 2014 15:10:06 GMT

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

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

perumal_41 — Fri, 26 Sep 2014 15:10:47 GMT

Hi Stuart,

PFA

hope it helps

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

JonnyPoole — Fri, 26 Sep 2014 15:15:24 GMT

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

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

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

Fri, 26 Sep 2014 15:17:27 GMT

works perfect - thanks

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

Fri, 26 Sep 2014 15:17:41 GMT

Thanks - I can see this would be of use also

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

Fri, 26 Sep 2014 15:17:58 GMT

Thanks - i might use this for a later iteration