Email domain Extractor & applying common domain against grouped records

[Masond3]

Hi Team, 

I need your help and drawing a blank.

Aim : 

• To extract the email domain from email for a given Id and put in a new column called "Email Domain"   
• To find the most common Email domain for a given Id and stamp on all the same ids in a column called "Common domain for Id" 

Input data

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

Expected outcome 

Id	Email	Email Domain	Common Domain for ID
0033a00002UsNUwAAN	abc@blueprintmedicines.com	@blueprintmedicines.com	blueprintmedicines.com or blueprintrg.com
0033a00002UsNUwAAN	abc@blueprintrg.com	@blueprintrg.com	blueprintmedicines.com or blueprintrg.com
003f1000022P0nJAAS	abc@healthadvances.com	@healthadvances.com	@healthadvances.com
001f100001OisCLAAZ	abc@10XGENOMICS.COM	@10XGENOMICS.COM	10xgenomics.com
001f100001OisCLAAZ	abc@10xgenomics.com	@10xgenomics.com	10xgenomics.com
001f100001OisCLAAZ	abc@10xtechnologies.com	@10xtechnologies.com	10xgenomics.com
001f100001OisCLAAZ	abc@10xgenomics.com	@10xgenomics.com	10xgenomics.com
001f100001OisCLAAZ	abc@its.jnj.com	@its.jnj.com	10xgenomics.com

Looking forward to your help 

This is what 
Regards
Masond3

[IraWatt]

Hey @Masond3,

Here is one way of doing this:

This solution uses Regex to find the Email Domain. If you want to learn more about Regex the community has some really quick interactive videos on getting to grips with it here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20...

Any questions or issues please ask! 
Hope that Helps

Ira Watt
Technical Consultant
Watt@Bulien.com 

[Masond3]

@IraWatt thanks for responding so quickly. The common Domain for Id is not being set correctly. 
I have included a data cleanse between the input and regex to normalize the emails ie ( put into lower case). 

For id : 001f100001OisCLAAZ there are 5 records, 3 of those 5 records have the domain of "@10xgenomics.com" Therefore the "Common Email domain for id" should say "@10xgenomics.com" not  "@10xgenomics.com or@10xgenomics.com or@10xtechnologies.com or@10xgenomics.com or@its.jnj.com"

[DataNath · Accepted solution]

Had a quick stab at this. Wasn't sure whether or not you wanted the @symbols removed in the common domain field as some have them removed and others don't in your example but easy enough to remove with a formula if not:

[Masond3]

@DataNath Love the handle ;) And this is exactly what  i am after.