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 linked half empty tables

Hi!

Consider the load script below:

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 would like to create a table with a calculated dimension with all email addresses where either the fields "Subscribed" and "Marketing" are both null, or no row exists in the Newsletter table. How can I achieve this?

I have tried things like:

=aggr(only({$-<Marketing={'1'}>-<Subscribed={'1'}>} Email), Email)

However, that doesn't give me the adress jay@cmail.com, which I want to include (as it is not associated with Marketing or Subscribed. How can I achieve this?

Cheers

Arvid 

2 Replies
Highlighted
Partner
Partner

Hi,

with this expression it seems to work:

=Aggr(If(Len(Concat(DISTINCT Marketing & Subscribed))=0,Only(Email)), Email)

Highlighted

Another option you can try is the following

=Aggr(Only({$<Email -= p({<Marketing = {'1'}>})+p({<Subscribed={'1'}>})>} Email), Email)