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

Use of Title Case vs RegEx for Last Names

Ruben_Navarro
5 - Atom

Hi all - first post ever here... I've failed at finding a solution on the community so far, so hope you can help.

I have a large record of employee names and the primary issue is that some of them, they are all uppercase.

 

My first thought was to use TitleCase() but noticed this method does not work when names have a hyphen or apostrophe. Other cases are last names starting with "Mc" or "Mac"... or last names with "van der"...

 

Is there a relatively easy solution to this?

For the time being, I have used a Filter tool to separate those with uppercase names from the rest, and applied TitleCase to them, and used Union to bring back together all records. It solves most of the issues, but there are still some previously uppercase names that were hyphenated or had an apostrophe.

 

Thanks! 

5 REPLIES 5
grossal
15 - Aurora
15 - Aurora

Hi @Ruben_Navarro,

 

can you test the Data Cleansing Tool on your data? It worked for me:

 

Input:

grossal_0-1585863037475.png

 

 

Setting:

grossal_1-1585863087085.png

 

Output:

grossal_2-1585863108159.png

 

Let me know if it works on your data.

 

Best

Alex

Ruben_Navarro
5 - Atom

Thanks for the suggestion @grossal

 

Unfortunately the Cleansing tool returns the same result as using TitleCase() through the Formula Tool. 

 

Sorry, I should have been a bit more specific with how the data looks. The issues appear with:

 

Jane McDoe -> Jane Mcdoe
Mickey Mouse-Disney -> Mickey Mouse-disney
Minnie O'Mouse -> Minnie O'mouse

 

... and so on.

 

Thanks again!

DavidP
17 - Castor
17 - Castor

You could use a replace function first to replace "-" with " - ", "Mc" and "Mac" with "Mc " and "Mac " and so forth, the do Titlecase followed by another replace to get rid of the spaces.

 

I think you'll have to deal with your edge cases on an individual basis.

grossal
15 - Aurora
15 - Aurora

Thanks for the sample @Ruben_Navarro.

 

I agree with @DavidP. I think you need to handle this with custom ruling and a replace function.

ThanyaWells123
5 - Atom

The above did not work for me can you please a formula

Labels