Splitting Columns

[jmcoveyou]

I am working on a project using data from the City of Chicago database, analyzing employee payroll and employee reimbursements. I'm having the issue that some of the name columns are simply first name and last name, while others are formatted such as "Last name, First name", "Last Name, First name Middle initial", "Last Name Jr (or Sr.), First Name) and a variation of those. One of my main goals is to find names that aren't in the payroll that are receiving employee reimbursements. However, with the way the names are formatted, it's hard to match names from the reimbursements to the payroll. I've tried a few things to separate the values and put them back together so they are all "First name Last name", but I have been unsuccessful. Does anyone know a way in Alteryx to fix this issue or at least get me on the right track? I have attached my tableau packaged workbook

[binuacs · Accepted solution]

@jmcoveyou One suggestion is to convert all the names into one format may be First Name Middle Initial Last name in all the input files and join with the join tool. I have attached a sample workflow below which converts the Last Name, First Name MI into Fname Mi LName. The logic is if the name contains any , then it split the name into Fname Mi Lname else keep the same name

[jmcoveyou]

Thank you for the idea! I will try that out and let you know how it goes

[jmcoveyou]

@binuacs Thank you for your help, that solved my problem perfectly!

[Idyllic_Data_Geek]

I've a similar use case that I;m trying to solve. I have Name column which is unstructured in the sense that some names have an initial for middle , some have full name and some don;t have middle name at all. I've created 2 new fields First Name and Last Name. And for an example QUENTIN W WORTHY should have last Name of Worthy and First Name of Quentin M. Similarly MATTHEW TAYLOR RODRIGUEZ should have last name of Rodriguez and First Name of Matthew Taylor

[ddiesel]

Hi again @Idyllic_Data_Geek!

I responded a little while ago to your other post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Parsing-Name-into-First-Name-and-Middl...

Please let us know if this helps!

Thanks,

Deb