String Functions

String functions are used for manipulating text values.

This article will cover the basic principles of working with these functions in the data load editor and visualisations, including:

  • The general function syntax
  • Working with string functions in the data load editor
  • Working with string functions in visualisations

For a complete, up-to-date list of string functions, please see the Qlik Sense help pages here.

The string function syntax

Not surprisingly, the general function syntax varies depending on the function. In this section, we will use an example function and decipher its syntax. This understanding will then help you read the syntax of other functions.

Example: SubField

The SubField() function extracts a substring from a full string that contains delimiters.

Copy code 


SubField(text, delimiter[, field_no ])

The rules for reading the function syntax are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ().
  2. The various elements you will need to enter are separated by commas. In this function, the elements are text, delimiter and [field_no]. The text element is the original string you will be splitting, the delimiter is the character on which you are splitting the string into parts such as a space or comma. The [field_no] is a parameter that lets you specify which part of the string to return, e.g. first, second etc.
  3. Anything between square brackets [] is an optional parameter. In this function, the optional parameter is the [field_no]. An optional parameter can be omitted. When used in a visualisation, the function will by default return the first part of the string if the parameter is omitted. In the data load editor, omitting the parameter would result in one part of the string per row, i.e. a string split into 5 parts given the delimiter will take 5 rows in the resulting table.

An example of this function being used with the optional parameters included would be:

Copy code 


SubField("Name",' ',1)

The resulting field would return the customer’s first name, provided the “Name” field contained the name in a ‘First Last’ format.

How to use string functions in the data load editor

Here are some important points on using string functions in the data load editor:

  • All of the functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
  • The functions can be nested and combined with other types of functions. Combining with other string functions can be particularly useful for extracting and transforming the data
  • The functions are particularly useful for businesses that have a lot of data stored as strings for either extracting a subset of information or just generally cleaning the data

Here is an example of the string functions SubField, Left and Index could be used together to extract specific parts of a string:

Copy code 


CUSTOMER_QUERIES:
LOAD
    "Customer ID",
    "Message",
    SubField("Message",';',1) as "Message DateTime",
    Left(SubField("Message",'(',2),Index(SubField("Message",'(',2),')')-1) as "Message Category"
    
FROM [queries.qvd] (qvd);

If we assume that each message will have a format of ‘DD/MM/YYYY hh:mm:ss; (Category) message string’, then the field “Message DateTime” will export the date and time part of the message. The code to extract the message category can be written using a few different combinations of string formulas. The way we’ve done it here, the code looks for the first open bracket and out of everything after it, it takes the number of characters before the closing bracket is encountered. As a result, the text contained between the brackets is returned as the “Message Category”.

How to use string functions in visualisations

Most string functions can be used in visualisations.

  • The functions can be nested and combined with other types of functions. Combining with other string functions can be particularly useful for extracting and transforming the data.
  • The functions can be used in dimensions or measures depending on your requirements.

Here is an example of a string data cleanse by using the Replace() function to remove ‘Ltd’ from customer names and the Upper() function to change the strings to uppercase. The formula used in the chart dimension is:

Copy code 


Upper(Replace([Customer Name],'Ltd',''))