Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

TextBetween Function

I was parsing data with a colleague when I learned about the TextBetween function. It is a simple function that can be used in a chart or in the script to return the character(s) that are between two delimiters you specify. Here is the syntax from Qlik Help:

TextBetween(text, delimiter1, delimiter2[, n])

The first parameter, text, is the input string. Delimiter1 is the first character to search for in the text while delimiter2 in the second character to search for in the text. The last parameter, n, is optional and is used to search for a specific delimiter pair. Let’s look at some examples.

The chart expression below returns 1234. The TextBetween function finds the first open parenthesis and the first close parenthesis and returns the text that is between them.

chart.png

 

Now if the text included multiple sets of data in parentheses, I can use the chart expression below to get the text in the third parentheses set.

chart2.png

 

This chart expression returns 3456 because I have set the fourth parameter to 3 in the TextBetween function which means I want the text between the third delimiter pair (the third pair of parentheses).

TextBetween can also be used in the script. In the script snippet below, I load 3 products into a table named Temp. Then I parse the Product field using TextBetween to create another field in the Products table named Product Code.

script.png

 

 

 

 

 

 

 

 

The result is a table like this:

table.png

 

 

 

 

In the past, I would have approached this parsing exercise differently. I would have used a combination of functions like the index function and the mid function to parse out the product code. Using the TextBetween function is so much easier, cleaner and more straight-forward. I cannot believe it took me this long to learn about it but I am glad I know about it now.

 

Thanks,

Jennell

3 Comments
Partner
Partner

Have a look at the subfield function

0 Likes
802 Views
Or
Valued Contributor II

@eddiewagtwrote:

Have a look at the subfield function

 


Subfield() can be used to generate similar results to TextBetween() in addition to its more significant usage for splitting data into rows. However, using it as a TextBetween() replacement is often quite inefficient. In the example above, you would need to use something like:

Purgechar(Subfield(Product,'('),')')

or

Left(Subfield(Product,'('),Len(Subfield(Product,'('))-1)

 

These would be necessary to trim the ending ')' that would be returned by the Subfield() function. You would need to use the latter if the string might have more text after the product number, e.g.

Product A (1234) - Yellow

730 Views
Partner
Partner

Good post!

0 Likes
233 Views