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 Email0033a00002UsNUwAANabc@blueprintmedicines.com0033a00002UsNUwAANabc@blueprintrg.com003f1000022P0nJAASabc@healthadvances.com001f100001OisCLAAZabc@10XGENOMICS.COM001f100001OisCLAAZabc@10xgenomics.com001f100001OisCLAAZabc@10xtechnologies.com001f100001OisCLAAZabc@10xgenomics.com001f100001OisCLAAZabc@its.jnj.com Expected outcome Id EmailEmail DomainCommon Domain for ID 0033a00002UsNUwAANabc@blueprintmedicines.com@blueprintmedicines.comblueprintmedicines.com or blueprintrg.com0033a00002UsNUwAANabc@blueprintrg.com@blueprintrg.comblueprintmedicines.com or blueprintrg.com003f1000022P0nJAASabc@healthadvances.com@healthadvances.com@healthadvances.com001f100001OisCLAAZabc@10XGENOMICS.COM@10XGENOMICS.COM10xgenomics.com001f100001OisCLAAZabc@10xgenomics.com@10xgenomics.com10xgenomics.com001f100001OisCLAAZabc@10xtechnologies.com@10xtechnologies.com10xgenomics.com001f100001OisCLAAZabc@10xgenomics.com@10xgenomics.com10xgenomics.com001f100001OisCLAAZabc@its.jnj.com@its.jnj.com10xgenomics.com Looking forward to your help This is what Regards Masond3

Email domain Extractor & applying common domain against grouped records

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

Common Use Cases

Accepted answers

DataNath

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:

Bring back email domains.yxmd

All comments

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%20Data

Any questions or issues please ask!
Hope that Helps

Ira Watt
Technical Consultant
Watt@Bulien.com

Workflow.yxmd

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

Bring back email domains.yxmd

Masond3

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703