Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Kalmer
Partner - Creator
Partner - Creator

Counting domain names from email field

Hello!

I haven't figured out how to count all the domain names from a email field.

So what do i have? I have the action field where i can see what the clients did in my sent out email. also in the actions i can see the email addresses so it should be pretty easy. The problem is not with the counting, but the show top 30 most used domains.


"count(distinct({$<action_type={'view', 'click'}>}action_email))" - easy part
+ count everything from '@xxx.com'

+ and show the most 30 used domains. (so in SQL there is a statement called LIMIT)

+ sort numerically Descending


Thank you in advance!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

This will be a lot easier if you extract the domain from the email in the script as a new field:

     subfield(action_email,'@',-1) as email_domain

Then you can simply use email_domain as dimension in a chart and use your count expression to count the email addresses per domain. You can use Dimension Limits (on the tab with the same name) to show the top 30 and you can sort the chart on the expression values in descending order.


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

This will be a lot easier if you extract the domain from the email in the script as a new field:

     subfield(action_email,'@',-1) as email_domain

Then you can simply use email_domain as dimension in a chart and use your count expression to count the email addresses per domain. You can use Dimension Limits (on the tab with the same name) to show the top 30 and you can sort the chart on the expression values in descending order.


talk is cheap, supply exceeds demand
jagan
Luminary Alumni
Luminary Alumni

HI,

Data:

LOAD

*,

subfield(action_email,'@',-1) as email_domain

FROM DataSource;


Now use Count(Distinct email_domain)


Hope this helps you.


Regards,

Jagan.



Kalmer
Partner - Creator
Partner - Creator
Author

Thank you. I was sure that i must have done it already in loading in the data. I thought that there would be a "know-how" how to do that without the field.
But it also solved my problem so Good job!.

So here's my email and domain load script:

!

Emails:

LOAD

  autonumber(email_email & email_created) as emailkey,

     autonumber(email_email & email_domain) as email_domain_ID,

  `email_active`,

    `email_created`,

    `email_domain` as email_status,

    `email_email`,

    `email_hash`,

    `email_lastupdate`,

    `email_optin`,

    `email_optintime`;

SQL SELECT `email_active`,

    `email_created`,

    `email_domain`,

    `email_email`,

    `email_hash`,

    `email_lastupdate`,

    `email_optin`,

    `email_optintime`

FROM emails;

Domains:

LOAD email_domain_ID,

  subfield(email_email,'@',-1) as email_domain_name

RESIDENT Emails;