I need to get All Occurrences in TextBetween()

[muhammedraleen]

Hi Team,

I have a text field (data type varchar), where it is there with all html tags. I need to extract the text in-between all the <o> tags.

When I use textbetween(Field,'<o>','</o>'), I get only the first occurrence. Somehow, I need to get all the occurrences in the Field.

Can anyone please help me on this...? Really very urgent matter

[BrunPierre]

Hi, I'd suggest you post sample data.

[muhammedraleen]

Hi @BrunPierre ,

I have no way to copy those text here. I have attached a screenshot below.

 

[Øystein_Kolsrud]

I guess it depends in what context and how you want to use the data, but "TextBetween" can take an optional argument "n" that indicates which occurrence to pick:

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions...

But how do you want to consume the result? Do you want it in the form of a list? And do you want to do this in the script or in an expression?

[muhammedraleen]

Hi @Øystein_Kolsrud ,

I need to extract all the occurrences, not a specific occurrence. This has to be done in the script.

The result needs to be shown in the table as a text.

[Øystein_Kolsrud · Accepted solution]

One option is to use the function "SubField" for this:

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions...

When you don't provide the optional index argument to that function, then it will return all occurrences. And then you trim the resulting values according to the start tag. Here's an example:

T_input:
Load * Inline [ T,
"This <o>is</o> the <o>text</o> i want.",
"<o>Here</o> <o>is</o> another <o>text</o> i want.".
"This has no text I want."];

// Get all subfields with the end tag.
T_tmp: Load T, SubField(Left(T, Index(T, '</o>', -1)-1), '</o>') as Entry Resident T_input;

// Drop the part before the start tag.
T: NoConcatenate Load T, SubField(Entry, '<o>', -1) as Entry Resident T_tmp;

Drop Table T_tmp;

It's a little cryptic, I know, but create an app with this and add a table with two fields "T" and "Entry" and you'll se how it works and can experiment with it to see if it fits your needs.

[muhammedraleen]

Hi @Øystein_Kolsrud ,

The above way you mentioned worked fine. It displays all the text occurrences between <o> tag, but in the table it displays in multiple rows. how can I combine all those to show in one row. I have attached a screenshot for your reference.

 

[Øystein_Kolsrud · Accepted solution]

One option to achieve that is to use the function "Concat":

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/StringAggr...

Remove the "Entry" field from your table and instead add this as a measure:

Concat(Entry, ', ')

[muhammedraleen]

Hi @Øystein_Kolsrud ,

It worked as expected.

Thank you very much for you great support and time.!