Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
jason_nicholas
Contributor II

Re: SubField Function with Complex Names

‌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

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
Contributor II

Re: SubField Function with Complex Names

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!