Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Miguel_Angel_Baeyens

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
Miguel_Angel_Baeyens

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

sunny_talwar

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.

ogautier62
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

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

sunny_talwar

To remove Blanks, use Trim() function

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

Anonymous
Not applicable
Author

It works.

Thank you very much!