Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
fluxfrog
Contributor II
Contributor II

Set analysis with null and multiple tables

Hi!

I have two tables coupled by one field, which is email. Unique emails exist in both tables, but some are common. I would like to use set analysis to list the emails of all customers that either don't have an entry in one of the tables, or has an entry, but where the value of a field is null. Here is a simple example:

Set NullInterpret = '';

Customer:
LOAD * Inline [
ID , Name , Email
123 , 'bob' , 'bob@cmail.com',
124 , 'penny' , 'penny@cmail.com',
125 , 'fitch' , 'fitch@cmail.com',
126 , 'clair' , 'clair@cmail.com',
127 , 'tom' , 'tom@cmail.com'
];

Newsletter:
LOAD * Inline [
Email, Subscribed, Marketing,
'bob@cmail.com', 1, 1,
'penny@cmail.com', , ,
'fitch@cmail.com', 1,
'tom@cmail.com', , 1,
'glenn@cmail.com', 1, 1,
'jay@cmail.com', , ,
];

I am trying to list all email addresses that are either not listed in the Newsletter table at all, or are listed, but has the fields "Subscribed" and "Marketing" set to null.

I have tried to create a table with a calculated dimension like this:
=aggr(only({$-<Marketing={'1'}>-<Subscribed={'1'}>} Email), Email)

But that doesn't work. jay is not included using that expression, and I guess it is because he is not listed as a Customer. How can I solve this?

0 Replies