topic Re: SubField Function with Complex Names in QlikView

SubField Function with Complex Names

jason_nicholas — Fri, 02 Feb 2018 17:48:53 GMT

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!

Re: SubField Function with Complex Names

tamilarasu — Fri, 02 Feb 2018 18:20:40 GMT

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"
];

Re: SubField Function with Complex Names

sunny_talwar — Fri, 02 Feb 2018 18:24:13 GMT

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"

];

Re: SubField Function with Complex Names

sunny_talwar — Fri, 02 Feb 2018 18:28:28 GMT

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

Re: SubField Function with Complex Names

tamilarasu — Fri, 02 Feb 2018 18:30:04 GMT

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.

Re: SubField Function with Complex Names

tamilarasu — Fri, 02 Feb 2018 18:36:26 GMT

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

Re: SubField Function with Complex Names

jason_nicholas — Fri, 02 Feb 2018 19:03:06 GMT

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!

Re: SubField Function with Complex Names

sunny_talwar — Fri, 02 Feb 2018 19:04:38 GMT

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

Re: SubField Function with Complex Names

jason_nicholas — Fri, 02 Feb 2018 19:10:03 GMT

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)

Re: SubField Function with Complex Names

sunny_talwar — Fri, 02 Feb 2018 19:23:54 GMT

Got confused in the last two names

1) Hayes Jr.,Michael Brian

2) Santos Melo Nogueira Dos Reis,Daniel J

Does this look right?

Re: SubField Function with Complex Names

jason_nicholas — Sat, 03 Feb 2018 05:13:10 GMT

‌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.

Re: SubField Function with Complex Names

tamilarasu — Sat, 03 Feb 2018 06:13:39 GMT

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"
];

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"
];

Let us know.

Re: SubField Function with Complex Names

jason_nicholas — Mon, 05 Feb 2018 14:08:45 GMT

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!