topic Re: How to Extract First Name Middle Name & Surname separately in App Development

How to Extract First Name Middle Name & Surname separately

P_Kale — Sat, 17 Feb 2024 15:09:08 GMT

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.

Re: How to Extract First Name Middle Name & Surname separately

theoat — Sat, 17 Feb 2024 18:18:01 GMT

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.

Re: How to Extract First Name Middle Name & Surname separately

rwunderlich — Sat, 17 Feb 2024 18:50:08 GMT

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

Re: How to Extract First Name Middle Name & Surname separately

theoat — Sat, 17 Feb 2024 19:29:40 GMT

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.

Re: How to Extract First Name Middle Name & Surname separately

P_Kale — Sun, 18 Feb 2024 10:50:29 GMT

Thanks @rwunderlich great.

Working fine.

Re: How to Extract First Name Middle Name & Surname separately

P_Kale — Sun, 18 Feb 2024 10:51:27 GMT

Thanks @theoat

Great.. Working Fine.

Re: How to Extract First Name Middle Name & Surname separately

theoat — Sun, 18 Feb 2024 10:53:48 GMT

With pleasure.

Have a nice day !

Re: How to Extract First Name Middle Name & Surname separately

P_Kale — Mon, 11 Mar 2024 06:39:54 GMT

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.

Re: How to Extract First Name Middle Name & Surname separately

theoat — Tue, 12 Mar 2024 10:15:31 GMT

Hello @P_Kale , how are you ?

Can you tell me the formula used ?

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.

Re: How to Extract First Name Middle Name & Surname separately

N30fyte — Tue, 12 Mar 2024 15:36:19 GMT

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!

Re: How to Extract First Name Middle Name & Surname separately

rwunderlich — Tue, 12 Mar 2024 17:22:00 GMT

But if the Name has only two names, -1 will return the lastname and 2 will return the same name as MiddleName.

I usually normalize multiple spaces to a single space using MapSubstring like this:

SpaceMap:
Mapping Load Repeat(' ', RecNo()+1), ' '
AutoGenerate 10;

and then in data load

MapSubString('SpaceMap', Name) as Name

-Rob

Re: How to Extract First Name Middle Name & Surname separately

P_Kale — Wed, 13 Mar 2024 06:36:45 GMT

Hi @theoat I am good.

Thanks for your time once again.

I am using below given formula to extract middle name and it has worked for most of the cases, But not works for few names one of the e.g. you can see the below formula works in 1st case but not worked in 2nd case. I have checked the spaces between words as suggested by @N30fyte but in single space is there between Middle and Surname.

SubField(PI_NAME_,' ',1) as PI_FirstName,

if(len(SubField(PI_NAME_,' ',3))>0, SubField(PI_NAME_,' ',2), '') as PI_MiddleName,

SubField(PI_NAME_,' ',-1) as PI_LastName,

& to concatenate First and Last Name

trim(SubField(PI_NAME_,' ',1)&' '&SubField(PI_NAME_,' ',-1)) as PI_FIRST_LAST_NAME,

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

Thanks in advance.

Re: How to Extract First Name Middle Name & Surname separately

P_Kale — Sat, 16 Mar 2024 10:09:12 GMT

Hi @theoat have you get time to work on this.