Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jason_nicholas
Creator II
Creator II

SubField Function with Complex Names

I have a list of names, which I am trying to separate into first, last, and middle. This works well for standard names with three parts, but is complicated where I have names with more parts. I am looking for a suggestion which will help manage this.

Source data is formatted Lastname,Firstname Middle names. That is, there is a comma (no spaces) delimiting last and first name, and spaces delimiting first name from any middle names.

Smith,Joe L

Wilson,Mike Mitchell

Lee,Johnny Lin Yang

I am using:

SubField([Full Name],',',1) as [Last Name],
SubField([Full Name],',',2) as [First Name],
SubField([Full Name],' ',2) as [Middle Name]

Which gives me

Smith              Joe                             L

Wilson            Mike                           Mitchell

Lee                Johnny Lin Yang         Lin

With longer, more complex names, my breakout doesn't work. I'm looking for:

Lee                 Johnny                         Lin Yang

What I would like to have is everything before the comma as last name (working properly), everything after the comma but before the first space as first name, and then everything after the first space, regardless of how many spaces follow, as middle name. Is there a way I can do the following?:

Add a step to the First Name function which gives me position 2 after a comma, but position 1 before a space in the same function?

Give me all the text after the first space in the Middle Name function?

If it helps, most names have 2, 3, or 4 parts as I have described here. But I have one name with 7 parts which I also want to see as First Name, Last Name, all 5 middle names combined.

I appreciate any advice!

12 Replies
jason_nicholas
Creator II
Creator II
Author

‌This looks great. I was able to work my way through the function, and I think I understand it.

I had written a different response, trying to dig into it a little more. I was hoping to remove part of the long name using a modified version of this function. The last name should be Reis, and I was trying to figure out how to just eliminate the excessive characters. But, I fell down a rabbit hole trying to over think it.

I will use a mapping load to convert the name into Reis,Daniel J before even starting this process. That way, this function  properly deals with the Jr and any future Jrs, and all I have to do is maintain a short mapping load list for the three or four names that are more trouble.

As always, thanks for your help. This board is amazing.

tamilarasu
Champion
Champion

Adding to sunny's solution.


Table:
LOAD *,
SubField([Full Name],',',1) as [Last Name],
TextBetween([Full Name] & ' ',',', ' ') as [First Name],
Trim(Mid(SubField([Full Name] & ' ', ',', 2), Index(SubField([Full Name] & ' ', ',', 2), ' ', 1) + 1)) as [Middle Name];
LOAD * INLINE [
Full Name
"Smith,Joe L"
"Wilson,Mike Mitchell"
"Lee,Johnny Lin Yang"
"Johnson,William"
"Hayes Jr.,Michael Brian"
"Santos Melo Nogueira Dos Reis,Daniel J"
]
;

Capture.PNG

If you say last name of Hayes Jr., Michael Brain is wrong, we can use if condition.


Table:
LOAD *,
If(WildMatch([Full Name],'*Jr.,*'),SubField([Full Name],',',1),Subfield(SubField([Full Name],',',1),' ',-1)) as [Last Name],
TextBetween([Full Name] & ' ',',', ' ') as [First Name],
Trim(Mid(SubField([Full Name] & ' ', ',', 2), Index(SubField([Full Name] & ' ', ',', 2), ' ', 1) + 1)) as [Middle Name];
LOAD * INLINE [
Full Name
"Smith,Joe L"
"Wilson,Mike Mitchell"
"Lee,Johnny Lin Yang"
"Johnson,William"
"Hayes Jr.,Michael Brian"
"Santos Melo Nogueira Dos Reis,Daniel J"
]
;

Capture.PNG

Let us know.

jason_nicholas
Creator II
Creator II
Author

For some reason, I don't get the same "Last Name" result for Daniel Reis as you do. I get Santos Melo Nogueira Dos Reis, although I copied your formula directly. This is academic, as I will use a mapping load to simply clean his name up before running this script and be done with it, and everything else works perfectly.

thank you both for your help!