Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ashok1203
Creator II
Creator II

Hi all, i have a column name as fullname. I want separate the fullname as firstname,midname,lastname. By using mid function how can we extract this.

Hi all, i have a column name as fullname. I want separate the fullname as firstname,midname,lastname. By using mid function how can we extract this.

ex: full name: Alexander De khoo  from this i want to separate

First name: Alexander

mid name: De

lastname: khoo

AAK
23 Replies
mphekin12
Specialist
Specialist

If you are always going to have a First, Mid and Last name you could use the following:

SubField(FullName, ' ', 1) as FirstName,

SubField(FullName, ' ', 2) as MidName,

SubField(FullName, ' ', 3) as LastName,

buzzy996
Master II
Master II

as mphekin12 said,u have to use the subfield function & based space position u can split name into 3 parts.

prabhas277
Creator
Creator

HI,

=mid('Alexander De khoo',1,10) returns  the "Alexander" u can treat as first name

=mid('Alexander De khoo',11,2) returns the De treat as second name

=mid('Alexander De khoo',14,4) returns the khoo treat as last name..

Hope it will work

regards,

suresh

buzzy996
Master II
Master II

hi, I hope name is dynamic here..so the above example won't work for all the possibilities .

he supposed to use subfield()!

ashok1203
Creator II
Creator II
Author

Hi,mphekin12 Thanks for reply, And your is correct But i want Using mid function how i can extract those.

AAK
ashok1203
Creator II
Creator II
Author

Hi prabhas277 , I want to apply whole column at a time by using mid function.

AAK
ankitaag
Partner - Creator III
Partner - Creator III

Hello Ashok,

Below is the script you can try to separate your FullName to FirstName, MiddleName and LastName:

Load mid(FullName,1,index(FullName,' ',1)-1) as FirstName,

mid(FullName,index(FullName,' ',1)+1,index(FullName,' ',2)-(index(FullName,' ',1)+1)) as MiddleName,

mid(FullName,index(FullName,' ',2)+1) as LastName

from Table

Explanation:

FirstName = mid(FullName,1,index(FullName,' ',1)-1) (1st parameter is the string, 2nd parameter is the starting index, 3rd parameter first calculates the index position of the 1st space and subtracting 1 resulting the last character before 1st space.)

MiddleName = mid(FullName,index(FullName,' ',1)+1,index(FullName,' ',2)-(index(FullName,' ',1)+1)) (1st parameter is the string, 2nd parameter first calculates the index position of the 1st space and adding 1 to get the index position of the character after the 1st space, 3rd parameter subtractes the index position of the 1st space and 2nd space)

LastName = mid(FullName,index(FullName,' ',2)+1) (1st parameter is the string, 2nd parameter first calculates the index position of the 2nd space and adding 1 to get the index position of the character after the 1st space, 3rd parameter is not specified which means till the end)


Thanks and Regards,

Ankita

jpenuliar
Partner - Specialist III
Partner - Specialist III

and how about in the case of two first Names?

ankitaag
Partner - Creator III
Partner - Creator III

Hi Jonathan,

Two first names as in?

Can you please be specific with an example?

Thanks and Regards,

Ankita