topic Re: how to load words of a string into a table in QlikView

how to load words of a string into a table

Anonymous — Mon, 25 Jun 2018 07:47:37 GMT

Hello

I'm blocked with this problem for several days ...

I can extract a string from a XML file and I can put it into a field of a table, by using LOAD FROM file (XmlSimple, Table is ...).

Now, rather than inserting the string, I would like to insert each word of this string (words are separated by « ; ») into the table. And there, I am blocked.

I handle well functions like « subfields », « substringcount »… to extract each word but I do not know where to put the loop (« for » or « do while ») in order to load each word of the string into the table…

Thanks a lot for your help!

Re: how to load words of a string into a table

Miguel_Angel_Baeyens — Mon, 25 Jun 2018 12:32:59 GMT

If you don't specify the third parameter in SubField(), it will create as many rows as different values, without any loop required:

SubField('Value1;Value2', ';') AS Field // will create 2 rows

SubField('Value1;Value2;Value3;Value4;ValueA', ';') AS Field // will create 5 rows

Re: how to load words of a string into a table

sunny_talwar — Mon, 25 Jun 2018 12:35:27 GMT

Would you be able to share few rows of data that you have and what exactly you would like to have as an output. It would also help if you can share what you have used as a script thus far.

Re: how to load words of a string into a table

ogautier62 — Mon, 25 Jun 2018 13:14:31 GMT

Hi,

what you need seems to look at this :

load ......, subfield(trim('your text field'),' ',iterno()) as word, ....

from 'your table' while iterno() <= substringcount(trim('your text field')," ")

so you have a loop (while iterno() for each text field which depends on number of blank char)

regards

Re: how to load words of a string into a table

Anonymous — Tue, 26 Jun 2018 08:09:42 GMT

It seems working by simply removing the third parameter. thank you for the tip !

However each word is extracted "as is", even with the blank space just before the word. It would not be a problem if all words would be with this blank space, but the first word has no blank space which makes the ordering (ascendent for example) not working.

Would you know how to normalize the format of the words (ie without blank space) ?

Thank you very much for your help

Re: how to load words of a string into a table

sunny_talwar — Tue, 26 Jun 2018 16:27:50 GMT

To remove Blanks, use Trim() function

Trim(SubField(....)) as ....

Re: how to load words of a string into a table

Anonymous — Wed, 27 Jun 2018 07:55:56 GMT

It works.

Thank you very much!