Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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)"
];
crusader_
Partner - Specialist
Partner - Specialist

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