Email domain Extractor & applying common domain against grouped records
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 | |
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 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
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@DataNath Love the handle ;) And this is exactly what i am after.