Team, I am having a mental block on the most basic of task and i feel silly asking this questions AIM : To find the total number of "Email domains" against a given ID Example data, Id Email Domain0033a00002UsNUwAANblueprintmedicines.com0033a00002UsNUwAANblueprintrg.com003f1000022P0nJAAShealthadvances.com001f100001OisCLAAZ10XGENOMICS.COM001f100001OisCLAAZ10xgenomics.com001f100001OisCLAAZ10xtechnologies.com001f100001OisCLAAZ10xgenomics.com001f100001OisCLAAZits.jnj.com Expected outcome Id Email DomainCount0033a00002UsNUwAANblueprintmedicines.com10033a00002UsNUwAANblueprintrg.com1003f1000022P0nJAAShealthadvances.com1001f100001OisCLAAZ10xgenomics.com3001f100001OisCLAAZ10xtechnologies.com1001f100001OisCLAAZits.jnj.com1 NB : I will apply a data cleanse on the email domain to make all text either Upper or Lower case, to help with the count Regards Masond3

Hey @Masond3 Here is my solution. * First, change the case of email ID to lower case using data cleansing tool. * Second, group by on ID and email ID and take count of the same. I hope this is what you are looking for. Regards, Amol Telore

Group email domains and Unique Count Against ID

Masond3

Team,

I am having a mental block on the most basic of task and i feel silly asking this questions

AIM : To find the total number of "Email domains" against a given ID

Example data,

Id	Email Domain
0033a00002UsNUwAAN	blueprintmedicines.com
0033a00002UsNUwAAN	blueprintrg.com
003f1000022P0nJAAS	healthadvances.com
001f100001OisCLAAZ	10XGENOMICS.COM
001f100001OisCLAAZ	10xgenomics.com
001f100001OisCLAAZ	10xtechnologies.com
001f100001OisCLAAZ	10xgenomics.com
001f100001OisCLAAZ	its.jnj.com

Expected outcome

Id	Email Domain	Count
0033a00002UsNUwAAN	blueprintmedicines.com	1
0033a00002UsNUwAAN	blueprintrg.com	1
003f1000022P0nJAAS	healthadvances.com	1
001f100001OisCLAAZ	10xgenomics.com	3
001f100001OisCLAAZ	10xtechnologies.com	1
001f100001OisCLAAZ	its.jnj.com	1

NB : I will apply a data cleanse on the email domain to make all text either Upper or Lower case, to help with the count

Regards
Masond3

Common Use Cases

Accepted answers

Amol_Telore

Hey @Masond3

Here is my solution.

First, change the case of email ID to lower case using data cleansing tool.
Second, group by on ID and email ID and take count of the same.

I hope this is what you are looking for.

Regards,

Amol Telore

New Workflow2.yxmd

All comments

Amol_Telore

Hey @Masond3

Here is my solution.

First, change the case of email ID to lower case using data cleansing tool.
Second, group by on ID and email ID and take count of the same.

I hope this is what you are looking for.

Regards,

Amol Telore

New Workflow2.yxmd

Masond3

@Amol_Telore Exactly what i want told you it was basic but having one of those silly moments lol

Masond3

@Amol_Telore and if i wanted to return the highest domain count for each id , how would i do that ?
In the scenarios where Email domain count is equal, is there anyway to flag those ?

Example
Id = 001f100001OisCLAAZ
Highest email domain is : 10xgenomics.com
with a count of 3

Id = 0033a00002UsNUwAAN
Joint highest domain email of : blueprintmedicines.com or blueprintrg.com
with a count of 1

MarshallG

Use Summarize Tool With a Group By on "Id" and "Email Domain" and Count of "Email Domain". Use Data Cleanse tool to modify the letter case.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703