topic Re: Subfield in QlikView

Subfield

renuka_sasikumar — Fri, 28 Feb 2014 09:47:55 GMT

Name

Parvinder Kaur

John P Gonsalve

Ritesh Gupta

Anita G Rodrigues

Deepali R Jahav

Rupesh J Sonawane

Fatima C Fernandes

Sunil S Singh

Rohit J Gupta

How do I remove the middle name and display the First Name n Last Name.

Re: Subfield

miha_pucelj — Fri, 28 Feb 2014 09:51:43 GMT

One option would be

subfield(Name, ' ' ,1) & ' ' & subfield(Name, ' ' ,3) As Name

Re: Subfield

Fri, 28 Feb 2014 09:54:33 GMT

try this

subfield(Name, ' ' ,1) & ' ' & subfield(Name, ' ' ,3) As Name_new

Re: Subfield

hic — Fri, 28 Feb 2014 09:58:06 GMT

Hard coding the number 3 in the subfield() function is not a good idea, since you then assume that there always is a middle name. Better then to use

subfield(Name, ' ' ,1) & ' ' & subfield(Name, ' ' ,-1) As Name

where -1 means the last subfield.

HIC

Re: Subfield

renuka_sasikumar — Fri, 28 Feb 2014 09:59:01 GMT

I tried this but i m getting Parvinder instead of Parvinder Kaur.

The name which does not have middle name its first name only cuming

Can u help me to get also Parvinder Kaur & Ritesh Gupta

Re: Subfield

MK_QSL — Fri, 28 Feb 2014 10:00:24 GMT

Something like below...

IF(Len(SubField(Name, ' ',2))>1, SubField(Name,' ',1)&' '&SubField(Name, ' ',2),

SubField(Name,' ',1)&' '&SubField(Name, ' ',3)) as NAME

Re: Subfield

Jason_Michaelides — Fri, 28 Feb 2014 10:00:37 GMT

I'm not sure the above solutions will work where there is no middle name. Maybe try something like:

SubField(Name,' ',1) & ' ' & SubField(Name,' ',-1)

Hope this helps,

Jason

Re: Subfield

Fri, 28 Feb 2014 10:01:39 GMT

Because you can have 2 or 3 (or more) words, I would also use substringcount() function

subfield(name, ' ', 1) & '-' & subfield(name, ' ', substringcount(name, ' ')+1) as newName

Fabrice

Re: Subfield

renuka_sasikumar — Fri, 28 Feb 2014 10:02:26 GMT

Thank u very much it worked.

Re: Subfield

Jason_Michaelides — Fri, 28 Feb 2014 10:05:04 GMT

There's something weird going on with the timing of posts appearing...! When I gave my answer, only the ones by Miha and Vish were present - even after posting and reloading. Then suddenly all the others appeared - included one from HIC the same as mine! Ah well - better to have too many answers than none at all!!

Re: Subfield

Jason_Michaelides — Fri, 28 Feb 2014 10:05:24 GMT

Make sure you mark a correct answer to close the thread.

Re: Subfield

Fri, 28 Feb 2014 10:08:27 GMT

try according to

Sir hic

subfield(Name, ' ' ,1) & ' ' & subfield(Name, ' ' ,-1) As Name

Regards

Re: Subfield

Fri, 28 Feb 2014 10:11:15 GMT

I always refresh before replying to a post - so that I know that nobody before me has given a better answer . I think the QlikView team should include / improve AJAX for this forum as well so that things get updated live.

Re: Subfield

MK_QSL — Fri, 28 Feb 2014 10:11:28 GMT

This is because sometimes members are replying to selecting reply of some other members rather than selecting the original post.

Re: Subfield

Jason_Michaelides — Fri, 28 Feb 2014 10:14:30 GMT

Still not sure why those responses didn't show either before or after I posted a reply. After refreshing my response was third on the list. Anyway - as I say - not a big deal. As long as a helpful answer is received!!

Re: Subfield

Fri, 28 Feb 2014 10:18:10 GMT

How about this? It works for the names without a middle name as well.

if(len(SubField(GetFieldSelections(Name),' ',2))=1, Replace(GetFieldSelections(Name),' ' & SubField(GetFieldSelections(Name),' ',2) &' ',' '), GetFieldSelections(Name))

Re: Subfield

renuka_sasikumar — Fri, 28 Feb 2014 10:21:11 GMT

I m new to qlikview n i think this is too confusing

Re: Subfield

sunilkumarqv — Fri, 28 Feb 2014 10:21:23 GMT

Like these

Please find the attached file you may see desired result

Re: Subfield

renuka_sasikumar — Fri, 28 Feb 2014 10:22:31 GMT

this is also working

Thank u