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!

1 Solution

Accepted Solutions
sunny_talwar

Got confused in the last two names

1) Hayes Jr.,Michael Brian

2) Santos Melo Nogueira Dos Reis,Daniel J

Does this look right?

Capture.PNG

View solution in original post

12 Replies
tamilarasu
Champion
Champion

Hi Jason,

Test:
Load *,
SubField(Names,',',1) as [Last Name],
Subfield(Names,' ', 2) as [First Name],
Mid(Names, Index(Names,' ',2)+1) as [Middle Name];
LOAD * INLINE [
Names
"Smith, Joe L"
"Wilson, Mike Mitchell"
"Lee, Johnny Lin Yang"
]
;

Capture.PNG

sunny_talwar

May be this

Table:

LOAD *,

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

TextBetween([Full Name],',', ' ') as [First Name],

Mid([Full Name], Index([Full Name], ' ', 1) + 1) as [Middle Name];

LOAD * INLINE [

    Full Name

    "Smith,Joe L"

    "Wilson,Mike Mitchell"

    "Lee,Johnny Lin Yang"

];

sunny_talwar

Tamil - I think you added a space after the comma, Jason specifically pointed out that there is no space

there is a comma (no spaces) delimiting last and first name
tamilarasu
Champion
Champion

I forgot that there was no space between last name and first name (only comma). So my solution is not correct. Yours should be the correct one.

tamilarasu
Champion
Champion

My bad, I realized my mistake after seeing your solution. Yours is correct, as usual.

jason_nicholas
Creator II
Creator II
Author

this worked great. There are a couple of strange artefacts with this method (people with no middle name, people with a Jr suffix, and people with a multi-part last name). I was going to try to dig into that a little further in this thread, but I think the best thing to do is work with it the way it is.

No middle names and Jr Suffixes only impact the Middle Name field, and it is a non-issue in my function.

The one name with 7 parts to it shows up very strangely, with only the First Name being correct. But this person only uses three parts, and I can probably just make that edit in the source documents rather than try to build a function.

thanks, as always!

sunny_talwar

If you can share one example from each of the cases, we might be able to get it to work

jason_nicholas
Creator II
Creator II
Author

Johnson,William:

Splits first and last correctly. Gives me Johnson,William as the middle name shown.

Hayes Jr.,Michael Brian:

First and Last are correct, including appending Jr. the last. Jr.,Michael Brian is the middle name shown.

Santos Melo Nogueira Dos Reis,Daniel J

First Name: Daniel

Last Name: Santos Melo Nogueira Dos Reis

Middle Name: Melo Nogueira Dos Reis,Daniel J

What I would want to see:

First Name: Daniel

Last Name: Reis

Middle Name: J  

Alternative Middle Name: J Santos Melo Nogueira Dos   (but that is a bit awkward, and Daniel J Reis would be appropriate)

sunny_talwar

Got confused in the last two names

1) Hayes Jr.,Michael Brian

2) Santos Melo Nogueira Dos Reis,Daniel J

Does this look right?

Capture.PNG