String Operators

[Jennell_McIntire]

There are two string operators that can be used in Qlik Sense and QlikView.  They are & (ampersand) and like.  While I use the ampersand all the time, I have never used like before but I will start after learning how easy it is to use.  The & operator is used to concatenate two strings.  I often use this when I want to combine text and the results of a calculation in a chart title or Text and Image object.  For example in the bar chart below from the Executive Dashboard demo, this expression is used for the title:

'Total Revenue by Product Group = ' & num(Sum([Sales Quantity]*[Sales Price]), '$#,##0')

The expression uses the ampersand to concatenate the string 'Total Revenue by Product Group = ' and the results of the total revenue calculation: num(Sum([Sales Quantity]*[Sales Price]), '$#,##0') into one string.  It will place the strings right after one another so do not forget to add spacing in between your strings if necessary.

The like operator has another purpose.  It compares two strings using wildcard characters and returns the Boolean value of True if the string before the operator matches the string after the operator.  The two wildcard characters that can be used in the string after the operator are * and ?.  The * represents any number of characters while the ? represents only one character.  Take a look at how this works in the examples below.

• ‘Qlik’ like ‘Q?ik’ will return True (-1) - the ? is a wildcard character for the ‘l’
• 'Qlik' like 'Q*' will return True (-1) - the * is the wildcard character for ‘lik’
• 'Qlik' like 'Q?k' will return False (0) – the ? is for a single character therefore it cannot represent the ‘l’ and the ‘i’
• 'Qlik' like 'Q??k' will return True (-1) – the first ? is the wildcard character for the ‘l’ and the second ? is the wildcard character for the ‘I’

The like operator can be used when you need to compare two strings that may vary slightly.  Assume you have a full list of products that look like this:

The like operator can be used to display products that start with ‘Product’ and end with the number 1.

The expression below could also have been used returning all products that end in 1.

If(ProductName like '*1', ProductName)

Ampersand and like are string operators that can be used in charts and in the script to concatenate or compare strings.  They are both binary operators meaning they take two operands.  When using the & operator, each string on either side of the & is an operand.  The same applies to the like operator with the operands being on each side of the like operator.  Happy Qliking!

Thanks,

Jennell