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: 
pkelly
Specialist
Specialist

Split Name Field

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

17 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sathishkumar_go
Partner - Specialist
Partner - Specialist

Hi,

Try This

1) create a mapping file

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
]
;

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

petter
Partner - Champion III
Partner - Champion III

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.

2016-02-12 #2.PNG

2016-02-12 #1.PNG

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.

petter
Partner - Champion III
Partner - Champion III

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.

jonathandienst
Partner - Champion III
Partner - Champion III

@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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
petter
Partner - Champion III
Partner - Champion III

Yes you are absolutely right - it would be better.

pkelly
Specialist
Specialist
Author

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.

petter
Partner - Champion III
Partner - Champion III

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]*