Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

how to load words of a string into a table

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!

1 Solution

Accepted Solutions
Highlighted

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

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

View solution in original post

6 Replies
Highlighted

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

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

View solution in original post

Highlighted

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

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.

Highlighted
Specialist II
Specialist II

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

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

Highlighted
Contributor II
Contributor II

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

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

Highlighted

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

To remove Blanks, use Trim() function

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

Highlighted
Contributor II
Contributor II

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

It works.

Thank you very much!