This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.

Learn more
I AGREE
Reject
Calling all Racers for the Alteryx Grand Prix! It's time to rev your engines and race to the stage at Inspire! Sign up here.
community Data Science Portal Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Email domain Extractor & applying common domain against grouped records

Masond3
8 - Asteroid
‎07-28-2022 02:25 AM

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
0033a00002UsNUwAANabc@blueprintmedicines.com
0033a00002UsNUwAANabc@blueprintrg.com
003f1000022P0nJAASabc@healthadvances.com
001f100001OisCLAAZabc@10XGENOMICS.COM
001f100001OisCLAAZabc@10xgenomics.com
001f100001OisCLAAZabc@10xtechnologies.com
001f100001OisCLAAZabc@10xgenomics.com
001f100001OisCLAAZabc@its.jnj.com

 

Expected outcome 

 

Id EmailEmail DomainCommon Domain for ID 
0033a00002UsNUwAANabc@blueprintmedicines.com@blueprintmedicines.comblueprintmedicines.com or blueprintrg.com
0033a00002UsNUwAANabc@blueprintrg.com@blueprintrg.comblueprintmedicines.com or blueprintrg.com
003f1000022P0nJAASabc@healthadvances.com@healthadvances.com@healthadvances.com
001f100001OisCLAAZabc@10XGENOMICS.COM@10XGENOMICS.COM10xgenomics.com
001f100001OisCLAAZabc@10xgenomics.com@10xgenomics.com10xgenomics.com
001f100001OisCLAAZabc@10xtechnologies.com@10xtechnologies.com10xgenomics.com
001f100001OisCLAAZabc@10xgenomics.com@10xgenomics.com10xgenomics.com
001f100001OisCLAAZabc@its.jnj.com@its.jnj.com10xgenomics.com

 

Looking forward to your help 

 

 

This is what 
Regards
Masond3

Labels:
Reply
0 Likes

Accepted SolutionSolved! Go to Solution.

4 REPLIES 4
IraWatt
17 - Castor
‎07-28-2022 02:30 AM

Hey @Masond3,

Here is one way of doing this:

IraWatt_0-1659000565511.png

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 

 

Workflow.yxmd
Reply
0 Likes
Masond3
8 - Asteroid
‎07-28-2022 02:36 AM

@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"

Reply
0 Likes
DataNath
17 - Castor
‎07-28-2022 02:39 AM

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:

 

DataNath_0-1659001138507.png

Bring back email domains.yxmd
Reply
0 Likes
Masond3
8 - Asteroid
‎07-28-2022 02:45 AM

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

Reply
Labels