Set analysis with linked half empty tables

fluxfrog — Thu, 10 Oct 2019 15:25:20 GMT

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

Re: Set analysis with linked half empty tables

StarinieriG — Fri, 18 Oct 2019 09:57:24 GMT

Hi,

with this expression it seems to work:

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

Re: Set analysis with linked half empty tables

sunny_talwar — Fri, 18 Oct 2019 11:30:48 GMT

Another option you can try is the following

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

topic Re: Set analysis with linked half empty tables in App Development

Set analysis with linked half empty tables

Re: Set analysis with linked half empty tables

Re: Set analysis with linked half empty tables