topic Breaking strings to fields in QlikView

Breaking strings to fields

prieper — Wed, 02 Dec 2009 19:20:34 GMT

Dears,

I am trying to convert some data, which come in a format like

100110 Durum wheat 100111 Seed 100119 Other 100190 Seed, White, Other
100200 Rye
100300 Barley
100400 Oats 100410 Seed 100480 Other

I would like to convert it into a databaseform like

CODE DECRIPTION
100110 Durum wheat
100111 Seed
100119 Other
100190 Seed, White, Other
100200 Rye
100300 Barley
100400 Oats
100410 Seed
100480 Other

Do you have an idea, how to break up the individual lines, of which each may have betw 1 and 100 different records?

Peter

AW:Breaking strings to fields

rbecher — Wed, 02 Dec 2009 19:40:13 GMT

Hi Peter,

how about a replace ' 1' (blank+1) with ';1' and then the use of subfield? This would work if code is starting with '1' every time..

load left(code_desc, 1 6) as CODE, mid(code_desc, 7) as DESCRIPTION;
load subfield(replace(field, ' 1', ';1'), ';') as code_desc from ...;

- Ralf

AW:Breaking strings to fields

prieper — Wed, 02 Dec 2009 20:22:53 GMT

Hi Ralf,

The cracking of the string into individual fields it not the problem, the problem I see, is how to convert them into into individual records?

So one idea might be to insert in the text-file at each code-number a CRLF, but have no clue, how to do.

Peter

AW:Breaking strings to fields

prieper — Wed, 02 Dec 2009 21:09:33 GMT

Tried to crack the lines with kind of brute force, with a loop counting from i=1 to 100 and then using the construct with Substring(Line, Digit, $(i)), but wonder whether there is a more elegant solution.

Peter

AW:Re: AW:Breaking strings to fields

rbecher — Wed, 02 Dec 2009 21:26:36 GMT

This will do the subfield function without the third parameter. Can you upload a sample? I would make a small app..

AW:Re: AW:Breaking strings to fields

rbecher — Wed, 02 Dec 2009 21:42:53 GMT

Hi Peter,

I made an example app with your data from the first post. There you can see how this goes..

- Ralf

AW:Re: AW:Breaking strings to fields

prieper — Wed, 02 Dec 2009 22:09:40 GMT

Works! Had only the three-parameter-variant in mind - sometimes it is worth to read the manual.

Thanks & Have a nice day!
Peter