topic Re: Split Name Field in QlikView

Split Name Field

pkelly — Fri, 12 Feb 2016 11:10:43 GMT

Hi...

I am importing from a csv file which has a user name column...

The data in this field is PaulKelly(6653) , Joe Bloggs(6621)...

I want to get the user name out and split into first name / last name...

e.g. PaulKelly(6653) would become Paul / Kelly

Any ides?

Thanks

Paul

Re: Split Name Field

sunny_talwar — Fri, 12 Feb 2016 11:12:31 GMT

If the first name first letter and last name first letter are the only two things which will be capitalized then you can probably try using a combination of PurgeChar(), Left(), SubField functions.

Re: Split Name Field

kaushiknsolanki — Fri, 12 Feb 2016 11:13:50 GMT

Hi Paul,

Everything is possible when you have proper logic in place.

In your case you need to let us know on what basis you will split the characters?

Do you have the space in between two words or is there any other logic?

Regards,

Kaushik Solanki

Re: Split Name Field

pkelly — Fri, 12 Feb 2016 11:15:47 GMT

Hi...

The data is stored as, for example, PaulKelly(6653)

I want to split..

Paul into First Name

Kelly into Last Name

Re: Split Name Field

MayilVahanan — Fri, 12 Feb 2016 11:17:29 GMT

Hi Paul,

If the space is available between first name and last name means, try like this

LOAD SubField(Name,' ',1) AS FirstName, Left(SubField(Name,' ', 2), FindOneOf(SubField(Name,' ', 2),'(')-1) AS LastName Inline

[

Name

Joe Bloggs(6621)

];

Re: Split Name Field

sunny_talwar — Fri, 12 Feb 2016 11:17:41 GMT

May be this:

=SubField('PaulKelly(6653)', Right(KeepChar('PaulKelly(6653)', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 1), 1) & ' / ' &

Right(KeepChar('PaulKelly(6653)', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 1) &

SubField(SubField('PaulKelly(6653)', Right(KeepChar('PaulKelly(6653)', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 1), 2), '(', 1)

Re: Split Name Field

sunny_talwar — Fri, 12 Feb 2016 11:19:20 GMT

Try something like this:

SubField('PaulKelly(6653)', Right(KeepChar('PaulKelly(6653)', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 1), 1) as FirstName,

Right(KeepChar('PaulKelly(6653)', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 1) &

SubField(SubField('PaulKelly(6653)', Right(KeepChar('PaulKelly(6653)', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 1), 2), '(', 1) as LastName

Replace PaulKelly(6653) with your field name above

Re: Split Name Field

awhitfield — Fri, 12 Feb 2016 11:19:22 GMT

HI Paul,

not sure you can do this if there's no space between first and last name.

Andy

Re: Split Name Field

kaushiknsolanki — Fri, 12 Feb 2016 11:19:38 GMT

Dear Paul,

I guess i couldn't make you understand what I wanted to say.

Now in your example which you gave.

PaulKelly(6653)

Joe Bloggs(6621)

If you see both words, in first word, First 4 characters are first name and remaining as Last name, but in Second example only 3 characters are first name and remaining all are Last Name.

So do you think that in all such word first 4 characters will be First Name?

If yes then it is easy, we can split using the functions which Sunny said.

Regards,

Kaushik Solanki

Re: Split Name Field

pkelly — Fri, 12 Feb 2016 11:20:08 GMT

Apologies, there is no space between the names - typo in my original text.

Re: Split Name Field

jonathandienst — Fri, 12 Feb 2016 12:04:50 GMT

This would be so much easier if QV supported regular expressions natively

Re: Split Name Field

sathishkumar_go — Fri, 12 Feb 2016 12:27:34 GMT

Hi,

Try This

1) create a mapping file

2) use MapSubString function to find the first & last name

subfield(MapSubString('Map_Name',Name),'_',2) AS FirstName,
subfield(MapSubString('Map_Name',Name),'_',3) AS LastName

Example:

Map_Name:
Mapping
LOAD * INLINE [
Name, MapNmae
A, _A
B, _B
C, _C
D, _D
E, _E
F, _F
G, _G
H, _H
I, _I
J, _J
K, _K
L, _L
M, _M
N, _N
O, _O
P, _P
Q, _Q
R, _R
S, _S
T, _T
U, _U
V, _V
W, _W
X, _X
Y, _Y
Z, _Z
];

Test:
LOAD * INLINE [
Name
PaulKelly(6653),
JoeBloggs(6621)
];

Test1:
load
Name,
subfield(MapSubString('Map_Name',Name),'_',2) AS FirstName,
subfield(MapSubString('Map_Name',Name),'_',3) AS LastName
resident Test;

drop table Test;

-Sathish

Re: Split Name Field

petter — Fri, 12 Feb 2016 13:20:21 GMT

You can actually do it like this with Regular Expressions by using a function written in VBScript and call it from the load script as a normal function. The VBScript function has to be put in the Tools / Module. I have attached the sample app that these screenshots are taken from. Even though the Load Script language have no Regular Expression functions you can use the RegEx support in VBScript.

The RegEx VBScript functions are from the QlikFix blog of BarryHarmsen and copied from his example application that you can find there. Search for Regular Expressions in QlikView on his blog.

Re: Split Name Field

petter — Fri, 12 Feb 2016 13:24:27 GMT

Well ... QV Load Script supports calling VBScript Functions out of the box. VBScript has a built-in RegEx object that can be used. So QlikView does not need any extension or calling external programs. Have a look at how it is done further down in this thread.

Re: Split Name Field

jonathandienst — Fri, 12 Feb 2016 13:31:56 GMT

@Petter

I know you can call them via VBScript, and somewhere I have a sample that does it, but I would like to see native support for Regex which is long overdue. I suspect that a properly designed native implementation will run a lot faster than the VBScript approach on a large data set.

Re: Split Name Field

petter — Fri, 12 Feb 2016 13:35:00 GMT

Yes you are absolutely right - it would be better.

Re: Split Name Field

pkelly — Fri, 12 Feb 2016 13:35:56 GMT

Thanks Petter,

Ended up with...

TelephoneSystem:
LOAD *,
SubField(TempParsedAgent,';',1 ) AS tel_FirstName,
SubField(TempParsedAgent,';',2 ) & IF(IsNull(SubField(TempParsedAgent,';',3)), '', SubField(TempParsedAgent,';',3)) AS tel_LastName;

LOAD
201511 AS %AnalysisPeriodKey,

PurgeChar(RIGHT(Agent,6), '()') AS TempExtension,

RegExFind(Agent , '[A-Z][a-z]*' , ';' , 0 ) AS TempParsedAgent

FROM (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Had to add to last name to cover names like McDonald.

Re: Split Name Field

petter — Fri, 12 Feb 2016 14:38:15 GMT

Here is the beauty (or ugliness - depending on your prespective) of regular expressions:

You can cater for MacXxxxxx McXxxxxx O'Xxxxxx X'Xxxxxx with this regex:

[A-Z]([a-z]{1,2}|')[A-Z][a-z]*|[A-Z][a-z]*