Qlik Community

Qlik Sense App Development

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

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

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