
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Thanks in advance.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @rwunderlich great.
Working fine.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @theoat
Great.. Working Fine.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
With pleasure.
Have a nice day !

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @P_Kale , how are you ?
Can you tell me the formula used ?
Enjoy your Qlik.
Kind regards,
Théo ATRAGIE.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- « Previous Replies
-
- 1
- 2
- Next Replies »