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 Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.

Grouping users by three fields

nikadomi
5 - Atom

Hi!

I need help with the following:

I want to group the user list by employees. Here is an example data.

IDShortnameEmailOutput
123asmithadam.smith@gmail.com1
123asmith1adam.smith1@gmail.com1
234asmithadam.smith1@gmail.com1
567asmith2adam.smith@gmail.com1
123asmith2adam.smith3@gmail.com1
987dlopezdan.lopez@gmail.com2

 

I know that 5 first records belong to one employee (by ID, Shortname or email). My expected output is in the "Output" column. My goal is to get information which IDs, Shortnames and emails are associated with the same employee (here in the example noted as "1").

 

Thank you everyone for help:)

1 REPLY 1
DanM
Alteryx Community Team
Alteryx Community Team

@nikadomi ,

 

 I would suggest starting with with identifying which entries are unique. In your sample, ID 987 Shortname Dlopez etc is unique, which now lowers the amount of data you are working with.

 

The next step I would identify how many ID's show more than once. You can use the Summarize tool with a group by ID and a count of the ID. Once you have identified all of the ID's that show more than once, I would filter those out and sort. Make sure that the IDs are the same person.

 

You've now eliminated the one's that are easy to identify. So at this point based on your sample data, you would end up with ID 234 and 567 that are not in a group.  From here, you could clean up the Shortname by removing #'s and characters and attempt to use the join tool  against the shortname from the last step to see what matches you get in the join. If you cleaned up the Shortname for 234 and 567 and join it, it would join to the first entry giving you the ID 123 which would confirm they are the same person. You could then join this back to the original data set and update the ID field.

 

Hope that gives you an idea

 

DanM

 

 

Once you have the data cleaned and somewhat matching in the three areas