topic Re: Separate names in QlikView

Separate names

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

Hi,

I've the follwoing names in a column:

Lastname, firstname and in some cases

lastname, firstnameLastname, firstname,...

The following lastname ist joined to the previous firstname without a symbol. There is only a line break.

e.g.:

Miller, James

Smith, Marc

Flesher, JonasRabo, David

Jaris, Martha

Holder, SvenVeno, SusenStark, JulieZery, Claus

How can I separate the names in the script if there are several in one cell?

Re: Separate names

MindaugasBacius — Thu, 08 Nov 2018 08:13:34 GMT

Have you tried Subfield:

LOAD SubField(Names, ',') as New_names

INLINE [

Names

"jim, bill"

john

];

Re: Separate names

martinpohl — Thu, 08 Nov 2018 08:51:00 GMT

see attached file

Re: Separate names

Anonymous — Thu, 08 Nov 2018 11:03:08 GMT

File?

Re: Separate names

martinpohl — Thu, 08 Nov 2018 11:06:26 GMT

can't you see attach?

Re: Separate names

Anonymous — Thu, 08 Nov 2018 11:11:05 GMT

The problem is, who to separate the lastname (person 2) form a privious firstname (Person 1).

Flesher, JonasRabo, David

I need:

"Flesher, Jonas" separated from "Rabo, David"

Re: Separate names

Anonymous — Thu, 08 Nov 2018 11:12:22 GMT

This is what I can see:

Re: Separate names

jonathandienst — Thu, 08 Nov 2018 11:15:45 GMT

Are you opening this from your inbox? You may need to be in a browser.

Re: Separate names

Anonymous — Thu, 08 Nov 2018 11:24:19 GMT

I'm using the Internet Explorer.

Re: Separate names

martinpohl — Thu, 08 Nov 2018 12:54:42 GMT

here is a better script:

Data:

LOAD * INLINE [
    Names
    "Miller, James"
    "Smith, Marc"
    "Flesher, JonasRabo, David"
    "Jaris, Martha"
    "Holder, SvenVeno, SusenStark, JulieZery, Claus"
];

// replace all letters with a pre | (pipe)
Data2:
load
replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
Names,'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')
,' |',' ') as Name // but clean | when space is in front
resident Data;
Data3:
load
SubField(Name,'|') as AllNames // seperate values between pipes
resident Data2;

Dataclean:
NoConcatenate
load
AllNames
resident
Data3
where len(trim(AllNames)) > 0; // clean datas

drop table Data3;

Re: Separate names

Anonymous — Thu, 08 Nov 2018 13:47:29 GMT

Great! Thanx!

Re: Separate names

martinpohl — Thu, 08 Nov 2018 14:08:45 GMT

please mark as helpful so other users can see the case is closed.

Regards

Re: Separate names

sasiparupudi1 — Thu, 08 Nov 2018 15:13:32 GMT

You can try something like below

Data:

LOAD * INLINE [

Names

"Miller,James"

"Smith,Marc"

"Flesher,JonasRabo,David"

"Jaris,Martha"

"Holder,SvenVeno,SusenStark,JulieZery,Claus"

];

Data1:

NoConcatenate Load

Names

Resident

Data

Where SubStringCount(Names,',')=1

;

Concatenate

Load

FindOneOf(Names,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',3)+1) as Names1

SubField(Replace(Names,Mid(Names,FindOneOf(Names,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',3)),' '& Mid(Names,FindOneOf(Names,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',3))),' ') as Names

Resident

Data

Where SubStringCount(Names,',')=2