topic Re: splitting a string in QlikView

splitting a string

sivarajs — Tue, 31 Jul 2012 09:23:27 GMT

Hi,

I want to split a string and need to take first letter of substring to make a new field

e.g: ABC TECH LTD as ATL

How can i acheive this?

Regards,

Sivaraj

Re: splitting a string

Tue, 31 Jul 2012 09:25:33 GMT

this is one string (one field)

three fields?

Re: splitting a string

Anonymous — Tue, 31 Jul 2012 09:48:53 GMT

You Can try something like this.. considering this is a single field value..

Left(SubField(Name, ' ', 1), 1) = This will get you the result A

Left(SubField(Name, ' ', 2), 1) = This will get you the result T

Left(SubField(Name, ' ', 3), 1) = This will get you the result L

Left(SubField(Name, ' ', 1), 1) & Left(SubField(Name, ' ', 2), 1) & Left(SubField(Name, ' ', 3), 1) = This will give you ATL

hope that helps..

Re: splitting a string

Anonymous — Tue, 31 Jul 2012 09:55:36 GMT

This example script should help you out:

Load

concat(Parts) as Done;

Load

left(subfield(F1,' '),1) as Parts;

Load * inline [

ABC TECH LTD

];

Re: splitting a string

Miguel_Angel_Baeyens — Tue, 31 Jul 2012 09:56:08 GMT

Hi,

Another option, not very orthodox but useful in case the field has not fixed number of elements (not always 3) would be the following:

=Concat(Left(SubField('ABC TECH LTD', ' ', ValueLoop(1, SubStringCount('ABC TECH LTD', ' ') +1, 1)), 1), '', ValueLoop(1, SubStringCount('ABC TECH LTD', ' ') +1, 1))

Hope that helps.

Miguel

Re: splitting a string

gandalfgray — Tue, 31 Jul 2012 10:09:28 GMT

Johannes example concatenates the Parts in random order,

and if there is more than one record in the inline table,

the initials from the different records get mixed.

So:

Load

concat(Parts) as Done;

Load

left(subfield(F1,' '),1) as Parts;

Load * inline [

ABC TECH LTD

Hello world

];

gives me a Done: AHLTw

I don't think that's what you want.

Re: splitting a string

swuehl — Tue, 31 Jul 2012 10:25:26 GMT

Probably not, but you can track the Text line and the subfield order like this:

TEXTS:

Load

recno() as TextID,

rowno() as PartID,

left(subfield(F1,' '),1) as Parts;

Load * inline [

ABC TECH LTD

Hello world

Beta Alpha

];

Load

TextID,

concat(Parts,'',PartID) as Done

resident TEXTS group by TextID;

Re: splitting a string

sivarajs — Tue, 31 Jul 2012 10:44:13 GMT

Hi Miguel,

Thanks that is working fine but valueloop throwing error in script

How can i acheive the same in script

Regards

sivaraj

Re: splitting a string

fah18 — Thu, 29 Apr 2021 02:59:23 GMT

Hi,

Did you find any solution to this? Im trying to use this for my excel Fields.. Any help?

Re: splitting a string

ArjunChandra — Thu, 29 Apr 2021 14:10:08 GMT

@swuehl , good Work. Forgive me for my incapability to understand your solution. I have few doubts

1) I tried to load Inline data 1st and then TEXTs Table and then resident table, why it is throwing error?

2) Why F1 field not coming into dashboard?

3) How to remove ' of ' if I have data like this ' International Inst of Tech'

Re: splitting a string

swuehl — Fri, 30 Apr 2021 16:14:50 GMT

In my script, the TEXTS table is created using an INLINE LOAD preceded by a preceding LOAD statement. I guess if you changed the order of the statements, you missed to add a RESIDENT source to the second LOAD.

I don't really understand your two other issues, would be better if you could post your current application / script.