Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

To add data to separate columns

Dear All,

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

IDText
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:

IDTextNewColumn1NewColumn2NewColumn3
1…text...Name1….text….Name2….text…..Name3….Name1Name2Name3
2…text...Name1….text….Name2….text…..Name3….Name1Name2Name3
3…text...Name1….text….Name2….text…..Name3….Name1Name2Name3
4…text...Name1….text….Name2….text…..Name3….Name1Name2Name3

Thank you in advance, James

7 Replies
Not applicable

Re: To add data to separate columns

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

Not applicable

Re: To add data to separate columns

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

renjithpl
Valued Contributor

Re: To add data to separate columns

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

Not applicable

Re: To add data to separate columns

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

Try the textbetween function.

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


talk is cheap, supply exceeds demand
Not applicable

Re: To add data to separate columns

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

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.


talk is cheap, supply exceeds demand
Community Browser