topic Re: How to divide a String in QlikView

How to divide a String

slondono — Mon, 04 Jan 2016 18:39:04 GMT

Hi everyone.

I need to separate a String that are contained in a dimension:

For example:

The dimension is 'Name'

"Edgar Allan Poe Clemm" ----> and i need to separate each name.

First LastName: Poe

Second LastName: Clemm

First Name: Edgar

Second Name: Allan.

This is obviously just an example, i have thousands of names in this format. I think one form to do that is separating with blank spaces and using 4 calculated dimensions but im not acquainted with the qlik functions for strings.

Can anyone help me?

Thanks to all, and sorry for my bad english,

Re: How to divide a String

sunny_talwar — Mon, 04 Jan 2016 18:58:47 GMT

May be this:

Table:

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

SubField(Name, ' ', 2) as [Second Name],

SubField(Name, ' ', 3) as [First LastName],

SubField(Name, ' ', 4) as [Second LastName];

LOAD * Inline [

Name

Edgar Allan Poe Clemm

];

Re: How to divide a String

maxgro — Mon, 04 Jan 2016 19:02:14 GMT

you can start with the substring function, like that (example for the load script, subfield works in chart too)

load

string,

SubField(string, ' ', 1) as s1,

SubField(string, ' ', 2) as s2,

SubField(string, ' ', 3) as s3,

SubField(string, ' ', 4) as s4

inline [

string

Edgar Allan Poe Clemm

a b c d

];

Re: How to divide a String

slondono — Mon, 04 Jan 2016 20:08:06 GMT

It works Thanks to you and maxgro‌

Re: How to divide a String

sunny_talwar — Mon, 04 Jan 2016 21:06:48 GMT

Awesome.

If you got what you were looking for, would you be able to close the thread by marking correct and helpful answers.

Best,

Sunny

Re: How to divide a String

MarcoWedel — Mon, 04 Jan 2016 22:01:40 GMT

Hi,

another solution could be:

LOAD *,
    SubField([Full Name],' ') as Name,
    Dual(AutoNumber(RowNo(),RecNo())&'. Name',AutoNumber(RowNo(),RecNo())) as NameNum
Inline [
    Full Name
    Edgar Allan Poe Clemm
    Samuel Langhorne Clemens
    Charles John Huffam Dickens
    Arthur Ignatius Conan Doyle
];

hope this helps

regards

Marco