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

[Amol_Telore · Accepted solution]

Hey @Masond3 

Here is my solution.

1. First, change the case of email ID to lower case using data cleansing tool.
2. 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

[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.