Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Specialist
Specialist

Re: 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.

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,

Highlighted
Master II
Master II

Re: 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.

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

Highlighted
Creator
Creator

Re: 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,

=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

Highlighted
Master II
Master II

Re: 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, I hope name is dynamic here..so the above example won't work for all the possibilities .

he supposed to use subfield()!

Highlighted
Creator II
Creator II

Re: 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,mphekin12 Thanks for reply, And your is correct But i want Using mid function how i can extract those.

AAK
Highlighted
Creator II
Creator II

Re: 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 prabhas277 , I want to apply whole column at a time by using mid function.

AAK
Highlighted
Partner
Partner

Re: 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.

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

Highlighted
Partner
Partner

Re: 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.

and how about in the case of two first Names?

Highlighted
Partner
Partner

Re: 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 Jonathan,

Two first names as in?

Can you please be specific with an example?

Thanks and Regards,

Ankita