Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

haymarketpaul
Contributor III

Displaying count of blank Emails

I was wondering if anyway could suggest the best way to display this...

I have a list of email addresses (see below).  Anyone who does not have an email address has <Not Answered> as their email address.

EmailImage.jpg

I need to be able to display the total number of people with an email address and also the total number of people who do NOT have an email address (i.e. <Not Answered>)

Any ideas how best to go about calculating and displaying this would be greatly appreciated.

Tags (1)
1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

Re: Displaying count of blank Emails

HI, Paul

If you want to use it to make selection you could merge the fields  EmailExists and NoExists in a single one

Customer:

LOAD CustomerID,

     If(Len(Trim([Email Address])) = 0, 1, 0) as NoEmail,

     If(Len(Trim([Email Address])) <> 0, 1, 0) as EmailExists,
     If(Len(Trim([Email Address])) <> 0, 'Exists', 'NoEmail') as Existence,

     If(Len(Trim([Email Address])) = 0, '<Not Answered>', [Email Address]) as Email

     FROM

CDSLayout.xls

(biff, embedded labels, table is [acs layout$]);

Regards,

Erich

4 Replies
Not applicable

Displaying count of blank Emails

you should flag them when you load your data .

Exemple

In the script

if (Email = '<NotAnswered>' , 1 , 0 ) as NotAnswered,

if (Email<>'<NotAnswered>', 1 , 0 ) as Answered

Then in your graphs , you sum the two values.

Philippe

haymarketpaul
Contributor III

Re: Displaying count of blank Emails

Thanks for the reply.

That does help me isolate and total them up. In fact i adopted your idea here as i was forcing in the <Not Answered> to make it selectable

Customer:

LOAD CustomerID,

     If(Len(Trim([Email Address])) = 0, 1, 0) as NoEmail,

     If(Len(Trim([Email Address])) <> 0, 1, 0) as EmailExists,

     If(Len(Trim([Email Address])) = 0, '<Not Answered>', [Email Address]) as Email

     FROM

CDSLayout.xls

(biff, embedded labels, table is [acs layout$]);

So i can now show the totals in a Statistics box but i would like them to be able to select one of the totals so they can filter the data down to show all the people with or without an email address.

Sadly a statistic box does not appear to be selectable

Any ideas?

erich_shiino
Honored Contributor

Re: Displaying count of blank Emails

HI, Paul

If you want to use it to make selection you could merge the fields  EmailExists and NoExists in a single one

Customer:

LOAD CustomerID,

     If(Len(Trim([Email Address])) = 0, 1, 0) as NoEmail,

     If(Len(Trim([Email Address])) <> 0, 1, 0) as EmailExists,
     If(Len(Trim([Email Address])) <> 0, 'Exists', 'NoEmail') as Existence,

     If(Len(Trim([Email Address])) = 0, '<Not Answered>', [Email Address]) as Email

     FROM

CDSLayout.xls

(biff, embedded labels, table is [acs layout$]);

Regards,

Erich

haymarketpaul
Contributor III

Re: Displaying count of blank Emails

Brilliant - thanks Erich that's perfect

Community Browser