Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
andymanu
Creator II
Creator II

Remove the first word and and anything after a specific character

Hi All,

I got a field with different data in it. For an example, the string got the job classification as the first word followed by the Name and sometimes (Grad) or (Post Grad) at the end. refer the below data set.

'ANUM COLLIN, Berry'

'NUM MARGE,  Jodie'

'NUM LEE, Mary (Post Grad)'

'RN TASKER,  Peter        (Grad)'

'RN McDONALD,  Ben (Grad)'

'NUM MORFITT, Sunny'

'RN VAN VARUN, Deb (Grad)'

I want to retrieve only the full name of the employee removing the first word and anything after "(" character. 

I used "Trim(SubField(Name,'(',1)) As NewName" to  remove anything after the "(" character and could get the first word removed from the string.

Can anyone help me with this?

Also, I found, even after using the Trim function, still I get some trailing spaces at the end of the NewName field. Would appreciate your comments on this too.

Thank you in advance.

Kind regards,

Andrew

 

 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

May be this

Table:
LOAD *,
	 Trim(Mid(SubField(Name & '(', '(', 1), Index(Name, ' ') + 1)) As NewName;
LOAD * INLINE [
    Name
    "ANUM COLLIN, Berry"
    "NUM MARGE,  Jodie"
    "NUM LEE, Mary (Post Grad)"
    "RN TASKER,  Peter        (Grad)"
    "RN McDONALD,  Ben (Grad)"
    "NUM MORFITT, Sunny"
    "RN VAN VARUN, Deb (Grad)"
];

View solution in original post

4 Replies
sunny_talwar

May be this

Table:
LOAD *,
	 Trim(Mid(SubField(Name & '(', '(', 1), Index(Name, ' ') + 1)) As NewName;
LOAD * INLINE [
    Name
    "ANUM COLLIN, Berry"
    "NUM MARGE,  Jodie"
    "NUM LEE, Mary (Post Grad)"
    "RN TASKER,  Peter        (Grad)"
    "RN McDONALD,  Ben (Grad)"
    "NUM MORFITT, Sunny"
    "RN VAN VARUN, Deb (Grad)"
];

View solution in original post

crusader_
Partner
Partner

Hi,

Try below:

DATA:
LOAD Name,
	MID(SUBFIELD(Name,',',1),INDEX(Name,' ')+1)&', '&SUBFIELD(trim(SUBFIELD(Name,',',2)),' ',1) AS NewName
;
LOAD * INLINE [
Name
"ANUM COLLIN, Berry"
"NUM MARGE,  Jodie"
"NUM LEE, Mary (Post Grad)"
"RN TASKER,  Peter        (Grad)"
"RN McDONALD,  Ben (Grad)"
"NUM MORFITT, Sunny"
"RN VAN VARUN, Deb (Grad)"
];
andymanu
Creator II
Creator II
Author

Hi Sunny,

Thank you very much.

Does the job perfectly.

Kind regards,

Andy

andymanu
Creator II
Creator II
Author

Hi Crusader,

Thank you very much for your prompt reply.

When there are additional spaces before the first name (after the ","), the expression does not give the expected outcome.

However, Sunny's expression worked. 

Nevertheless, thank you very much for your excellent feedback.

Kind regards,

Andy