Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andymanu
Contributor

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

Re: Remove the first word and and anything after a specific character

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)"
];
4 Replies

Re: Remove the first word and and anything after a specific character

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)"
];
Partner
Partner

Re: Remove the first word and and anything after a specific character

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
Contributor

Re: Remove the first word and and anything after a specific character

Hi Sunny,

Thank you very much.

Does the job perfectly.

Kind regards,

Andy

andymanu
Contributor

Re: Remove the first word and and anything after a specific character

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