ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Identify LAST FIRST and FIRST LAST names as same Person

jessfarthing
6 - Meteoroid

 Hi,

 

I have a data set with only one Employee Name column and noticed that this data interchanges the naming convention between "LAST NAME  FIRST NAME" and "FIRST NAME LAST NAME" throughout the file.  

 

If I have both "John Smith" and "Smith John" in the Employee Name field in the data that is meant to reflect the same person, is there an easy way to use a tool to detect this is the same person?  From what I can see, the Unique tool would only identify duplicates based on multiple lines containing "John Smith" and multiple lines containing "Smith John" as they are demonstrated in the data.  And there is no other determinate I can find to relate a pattern to when a certain naming convention is used. 

 

Thanks!

 

 

MarqueeCrew
19 - Altair
19 - Altair

It sounds like the name field has a comma in it when the names need to be reversed. Maybe you should create a working name field where you put the name without a comma and construct the name when a comma is present. 

you can either use an if statement or you can filter out the names with a contains condition and you find a comma. 

 

 Try using getword() to make this easier. When more than 2 words are present you might need more logic. 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
jessfarthing
6 - Meteoroid

Hi Mark,

 

I should clarify that there's no comma in the data actually and maybe should have not written that in.  The data has different variations of capitalisation and spacing between names. 

MarqueeCrew
19 - Altair
19 - Altair
What about using a recordid on the field as a key and use a text to columns on the name field. Use a space asa delimiter and output to rows. Now sort on the names and then use a summarize to concatenate them into a single field grouped by the recordid.

Now you can join the output back and you'll have a field with an alphabetical version of the names. Mark Frisch becomes Frisch, Mark and Frisch Mark becomes Frisch, Mark.

Cheers,

Mark

Sent from my iPhone
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
benakesh
12 - Quasar

Hi @jessfarthing ,

You may want to check Fuzzy match tool  . This tool matches  names  and it is matching first -last with last-first. 

benakesh_0-1575258458139.png

 

Labels