topic Re: Separating two words in QlikView

Separating two words

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Is there a way in QlikView to separate two words at the transformation step into two different words as is the case in MS SQL Server.

For example if I have a name Chris Chitemerere, i can separate this into two new derived columns namely, First Name (Chris) and Second Name (Chitemerere).

Is this possible in QV?

Regards.

Chris

Re: Separating two words

sunny_talwar — Tue, 22 Mar 2016 20:38:30 GMT

I guess the only way I can think of is to use SubField function

SubField(FieldName, ' ', 1) as [First Name],

SubField(FieldName, ' ', 2) as [Last Name],

Re: Separating two words

swuehl — Tue, 22 Mar 2016 20:39:21 GMT

Maybe like

LOAD

Subfield(FULLNAME, ' ',1) as FirstName,

Subfield(FULLNAME, ' ',2) as LastName,

...

or using other string functions, there are a bunch of functions in QV.

Re: Separating two words

Anonymous — Tue, 22 Mar 2016 20:58:00 GMT

THANKS Sunny

Re: Separating two words

Anonymous — Tue, 22 Mar 2016 20:58:27 GMT

Thanks Swuehl

Re: Separating two words

Anonymous — Tue, 22 Mar 2016 21:04:41 GMT

Hi Sunny

What if i have Chris Moore Chitemerere and i want 'Chris More' as FirstandSecondName?

Regards

Re: Separating two words

swuehl — Tue, 22 Mar 2016 23:37:28 GMT

This script assumes that your first names and last name are separated by spaces, and last name is only consisting of one word:

LOAD *,

Left(FULL, Index(FULL,' ',-1)-1) as FirstSecond,

Subfield(FULL, ' ',-1) as Last;

LOAD * INLINE [

FULL

Chris Moore Chitemerere

Chris Chitemerere

];

Re: Separating two words

Anonymous — Wed, 23 Mar 2016 11:36:49 GMT

Thanks Swuehl