Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
P_Kale
Creator II
Creator II

How to Extract First Name Middle Name & Surname separately

Hi,

How to Extract First Name Middle Name & Surname separately. For e.g

I have name in one column "Madhu Anant Thorat" but i want this to be divided and want to create 3 different columns like 

Madhu in NAME column, Anant in MIDDLE NAME column, THORAT in SURNAME column.

But if Name in column "RAJ DIXIT" then RAJ in NAME column and DIXIT in SURNAME column.

how i can handle this.

@marcus_sommer 

Thanks in advance.

Labels (1)
1 Solution

Accepted Solutions
theoat
Partner - Creator III
Partner - Creator III

Good idea @rwunderlich !

So, to recap :

TEST:
Load
SubField(Name,' ',1) as FirstName,
SubField(Name,' ',-1) as LastName,
if(len(SubField(Name,' ',3))>0, SubField(Name,' ',2), '') as MiddleName
Resident NAMETABLE;

Kind regards,

Théo ATRAGIE.

View solution in original post

12 Replies
theoat
Partner - Creator III
Partner - Creator III

Try this in the load script :
TEST:
Load
SubField(Name,' ',1) as FirstName,
SubField(Name,' ',-1) as LastName,
if(SubField(Name,' ',2)<>SubField(Name,' ',-1),SubField(Name,' ',2)) as MiddleName
Resident NAMETABLE;

Kind regards,
Théo ATRAGIE.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

On the chance that middle and surnames are the same, i would modify MiddleName to

if(len(SubField(Name,' ',3))>0, SubField(Name,' ',2), '') as MiddleName

-Rob

theoat
Partner - Creator III
Partner - Creator III

Good idea @rwunderlich !

So, to recap :

TEST:
Load
SubField(Name,' ',1) as FirstName,
SubField(Name,' ',-1) as LastName,
if(len(SubField(Name,' ',3))>0, SubField(Name,' ',2), '') as MiddleName
Resident NAMETABLE;

Kind regards,

Théo ATRAGIE.

P_Kale
Creator II
Creator II
Author

Thanks @rwunderlich great.

Working fine.

P_Kale
Creator II
Creator II
Author

Thanks @theoat 

 Great.. Working Fine.

theoat
Partner - Creator III
Partner - Creator III

With pleasure.

Have a nice day !

P_Kale
Creator II
Creator II
Author

Hi @theoat  though the thread is resolved.

I have faced one problem in few names like highlighted in sr.no. 2. Here i have not get surname but in 1st case i have get the surname. So don't understand why this has happened.

Sr.no PI_NAME PI_FIRST_LAST_NAME
1 SURESH KUMAR CHAUDHARY SURESH CHAUDHARY
2 TAPASH SUKLA DAS TAPASH

 

Thanks in advance.

theoat
Partner - Creator III
Partner - Creator III

Hello @P_Kale , how are you ?

Can you tell me the formula used ?

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.

N30fyte
Creator
Creator

My guess is there are two spaces between 'SUKLA' and 'DAS'.

You should be able to resolve this for all cases by modifying the fifth line of the excellent script as follows:

if(len(SubField(Name,' ',-1))>0, SubField(Name,' ',2), '') as MiddleName

But there will still be issues if there are two spaces between the first name and the middle name.

Hope this helps!