Alteryx designer Discussions

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

Dynamic find and replace substring in field 1 with value of field 2 on record level

Highlighted
6 - Meteoroid

Hi guru's,

 

I am in the process of cleaning up customer data in our system. One of the issues I have run into is that at times the last name field contains the full customer name ('John Smith') and the first name field has the first name value as well ('John'). In reporting this will concatenate wrong ('John Smith, John').

 

I am looking for a method where I can use the value of first name and see if this is a substring of last name and then remove this substring in last name. So basically getting 'Smith' and 'John' in separate fields.

 

I have tried the find and replace tool. However this does not seem to work on record level, but rather over all records. All first name strings will be compared with all last names strings and if any match is found that substring will be removed. As a result records that had a last name 'John Smith' but were the first name was empty, are changed to 'Smith'. I would like the logic to work at record level only.

 

Could anyone assist me in this?

Alteryx
Alteryx

Hi @Gudo 

 

You could use the Formula - GetWord([Last Name], CountWords([Last Name])-1) within the Formula tool.

 

This will count the number of words within your last name field and make sure to only take the last one.

 

Would this work for you?

 

Thanks

Will

Highlighted
Alteryx Partner

I put this into a workflow so you can see how it works clearly on a record level. There are a couple ways to do this but this is probably the simplest way

 

Highlighted
Alteryx Partner

Hi @Gudo,

 

I'd go about this in a slightly different way and simply use the following formula:

 

TRIM(Replace([Last Name], [First Name], ''))

 

This would take an input like this:

image.png

 

And finish with an output of:

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download.

 

Regards,

Jonathan

Highlighted
6 - Meteoroid

Thanks for the suggestion Will. Unfortunately it only does part of the trick. The order in which the words in the last name is put varies and sometimes you cannot distinguish the words (Johnsmith). 

Highlighted
6 - Meteoroid

John,

 

This seems to work for my problem. The test cases I have all turn out as they should. I will have our testers go through my output next week and see if they can still manage to find or create cases in which this logic does not work.

 

Thanks!

Highlighted
Alteryx Partner

Great, feel free to post back on this thread if you have any further issues on it @Gudo!

Labels