Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Group email domains and Unique Count Against ID

Masond3
8 - Asteroid

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
0033a00002UsNUwAANblueprintmedicines.com
0033a00002UsNUwAANblueprintrg.com
003f1000022P0nJAAShealthadvances.com
001f100001OisCLAAZ10XGENOMICS.COM
001f100001OisCLAAZ10xgenomics.com
001f100001OisCLAAZ10xtechnologies.com
001f100001OisCLAAZ10xgenomics.com
001f100001OisCLAAZits.jnj.com

 

Expected outcome 

 

Id Email DomainCount
0033a00002UsNUwAANblueprintmedicines.com1
0033a00002UsNUwAANblueprintrg.com1
003f1000022P0nJAAShealthadvances.com1
001f100001OisCLAAZ10xgenomics.com3
001f100001OisCLAAZ10xtechnologies.com1
001f100001OisCLAAZits.jnj.com1


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

4 REPLIES 4
Amol_Telore
11 - Bolide

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.

Amol_Telore_0-1658938831663.png

I hope this is what you are looking for.

 

Regards,

Amol Telore

Masond3
8 - Asteroid

@Amol_Telore  Exactly what i want :) told you it was basic but having one of those silly moments lol 

Masond3
8 - Asteroid

@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
8 - Asteroid

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.

 

MarshallG_0-1658939137959.png

 

Labels