Set analysis with null and multiple tables

fluxfrog — Thu, 10 Oct 2019 06:32:36 GMT

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?

topic Set analysis with null and multiple tables in App Development

Set analysis with null and multiple tables