topic Re: Text String extraction in QlikView

Text String extraction

Mon, 30 Mar 2015 14:15:24 GMT

Dear Community,

I have a text string that is rather large - it captures all activity associated to one company. It does however have some rules set against it.

I am trying to find the text associated with "Meeting 1" which can occur at any point in the string. It will be captured as:

[Date Time] Name - Meeting 1 - blah blah blah

[Date Time] Name - Another activity - blah blah blah

What i would like to do is capture the "blah blah blah" in a sepertate text box & then, if possible, capture the next meeting in another box (the second occurance of "Meeting 1")

I've tried:

mid(TEXT FIELD, findoneof(TEXT FIELD, 'Meeting 1')) as a start but this isn't working correctly.

I need to find the text after "Meeting 1" and before the next "["

Many thanks

Drew

Re: Text String extraction

Anonymous — Mon, 30 Mar 2015 14:17:57 GMT

Try

textbetween(FieldName, 'Meeting 1','[')

Re: Text String extraction

Peter_Cammaert — Mon, 30 Mar 2015 14:25:57 GMT

TextBetween(FieldName, 'Meeting 1 - ', '[')

if you don't need the hyphen and whitespace...

Re: Text String extraction

Mon, 30 Mar 2015 14:26:12 GMT

That returns a null value - there could be various times when this would occur within the text field.... maybe that is causing the trouble?

Re: Text String extraction

Anonymous — Mon, 30 Mar 2015 14:31:54 GMT

Various times is not a problem it should return the 1st occurrence. Unless you tell to return another one

textbetween(FieldName, 'Meeting 1','[', 2)

Check the spelling and case, and number of spaces if you use them. Try in a text box first.

Re: Text String extraction

robert_mika — Mon, 30 Mar 2015 14:35:36 GMT

...or post few examples...

Re: Text String extraction

Mon, 30 Mar 2015 14:49:07 GMT

I've copied and pasted from the notepad so it is exactly as it appears but doesnt work. It will work if I use ,'[','['

Seems to be struggling with the full "Meeting 1' but i do not know why.

I've formatted the field as text to try to make sure that isn't the issue and it doesn't change the outcome.

sensitive information so can't post my example, sorry.

Re: Text String extraction

Mon, 30 Mar 2015 14:55:40 GMT

function works in Excel - must be a formatting thing?

Re: Text String extraction

robert_mika — Mon, 30 Mar 2015 14:59:00 GMT

Which function?

Re: Text String extraction

Mon, 30 Mar 2015 14:59:01 GMT

I was being thick, sorry

Re: Text String extraction

Anonymous — Mon, 30 Mar 2015 15:03:50 GMT

This works fine in a text box:

=trim(TextBetween('any text Meeting 1 text to select [ more of any text','Meeting 1','['))

(I added trim() to remove leading and trailing spaces)