topic Re: To add data to separate columns in QlikView

To add data to separate columns

Wed, 24 Oct 2012 13:54:18 GMT

Dear All,

I would like to ask you for help. I am loading this table from .xls:

ID	Text
1	…text...Name1….text….Name2….text…..Name3….
2	…text...Name1….text….Name2….text…..Name3….
3	…text...Name1….text….Name2….text…..Name3….
4	…text...Name1….text….Name2….text…..Name3….
…	…

I do not know, how I can determine & divide Name1/Name2/Name3 from string Text and add to separate columns. The expected result should be:

ID	Text	NewColumn1	NewColumn2	NewColumn3
1	…text...Name1….text….Name2….text…..Name3….	Name1	Name2	Name3
2	…text...Name1….text….Name2….text…..Name3….	Name1	Name2	Name3
3	…text...Name1….text….Name2….text…..Name3….	Name1	Name2	Name3
4	…text...Name1….text….Name2….text…..Name3….	Name1	Name2	Name3
…	…	…	…	…

Thank you in advance, James

Re: To add data to separate columns

Wed, 24 Oct 2012 14:00:46 GMT

can u post sample data for text...Name1.....text....Name2 ?

Re: To add data to separate columns

Wed, 24 Oct 2012 14:08:27 GMT

Of course, here it is an example:

***text text text text 26-Aug-2011 08:57 (GMT+2:00) added by Name1 text text text***text text text text 27-Aug-2012 01:45 (GMT+2:00) added by Name2 text text text***text text text text 12-Aug-2011 03:12 (GMT+2:00) added by Name3

Thank you, James

Re: To add data to separate columns

renjithpl — Wed, 24 Oct 2012 14:50:42 GMT

as per your text, you can try for

Name1

= subfield(trim(subfield(Text, 'added by', 2)), ' ', 1)

Name2

=subfield(SubField(Text, ' added by ', 3), ' ', 1)

Name 3

=SubField (Text, ' added by ', 4)

You can try accoring your string,

hope this helps

Re: To add data to separate columns

Wed, 24 Oct 2012 15:04:29 GMT

Hi renjithpl,

thanks for answer, this is probably the right way.

However, i have not noticed, that there should be a lot of names in the string and they are composed from first name and last name [John Doe], so this solution is not the "best fit" for me.

Please see the original loaded data:

14-Dec-2011 10:41 (GMT+1:00) added by John Doe 1 (PID-004933):

TEXT TEXT TEXT

*************************************************************************************

14-Dec-2011 09:49 (GMT+1:00) added by John Doe 2 (PID-003104):

TEXT TEXT TEXT

*************************************************************************************

12-Dec-2011 15:03 (GMT+1:00) added by John Doe 3 (PID-003405):

TEXT TEXT TEXT

*****************************************************************************************

02-Dec-2011 08:46 (GMT+1:00) added by John Doe 4 (PID-003164):

TEXT TEXT TEXT

************************************************************************************

and more…

Re: To add data to separate columns

Gysbert_Wassenaar — Wed, 24 Oct 2012 16:35:57 GMT

Try the textbetween function.

trim(textbetween(Text,'added by','(PID')))

Re: To add data to separate columns

Thu, 25 Oct 2012 06:39:08 GMT

Hello Gysbert,

I have tried function textbetween, it works perfectly, but unfortunately only for first name in the string..

James

Re: To add data to separate columns

Gysbert_Wassenaar — Thu, 25 Oct 2012 08:41:39 GMT

Oh, I thought you had more lines of text, not one long string. If you have a fixed number of names you can add an index to textbetween as second parameter to get the n-th match. Otherwise I'd first use subfield to create seperate records.