topic Split fullname to LastName, FirstName, and MI in QlikView

Split fullname to LastName, FirstName, and MI

Anonymous — Thu, 24 Aug 2017 17:45:28 GMT

Hi,

We have a FullName field with LastName,FirstName Middlename format. I need to split lastname, firstname, and MI to separate columns. Could someone please help? I need to do it through the expression and not the script itself. Right now, I was able to get LastName using SubField(Name,',',1)

Current FullName format:

FullName Format
Smith,James R
Smith,James Randy
Smith,James Randy JR

I need it to separate it like below:

LastName	FirstName	MI
Smith	James	R
Smith	James	R
Smith	James	R

Thank you.

Re: Split fullname to LastName, FirstName, and MI

Peter_Cammaert — Thu, 24 Aug 2017 17:52:04 GMT

For FirstName you can use

Subfield(Subfield(Name, ',', 2), ' ', 1)

And for MI (which I assume contains only the initial character):

Left(Subfield(Subfield(Name, ',', 2), ' ', 2), 1)

The operations are self-explaining. If not sure about what is happening here, ask away.

[Edit] Right, it's MiddleInitial not MiddleName.

Re: Split fullname to LastName, FirstName, and MI

Anonymous — Thu, 24 Aug 2017 17:56:12 GMT

Thank you so much. That worked like a charm.

Re: Split fullname to LastName, FirstName, and MI

MarcoWedel — Thu, 24 Aug 2017 19:25:11 GMT

Hi,

depending on how reliable spaces occur in your data, one solution might be also:

LOAD [FullName Format],
    SubField([FullName Format],',',1) as LastName,
    TextBetween([FullName Format],',',' ') as FirstName,
    Mid([FullName Format],Index([FullName Format],' ')+1,1) as MI
FROM [https://community.qlik.com/thread/272390] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Split fullname to LastName, FirstName, and MI

Anonymous — Mon, 28 Aug 2017 17:01:08 GMT

Wow! Thank you.