Qlik Sense String Functions – Syntax and Example
We offer you a brighter future with industry-ready online courses - Start Now!!
1. Objective
Earlier, we have discussed Qlik Sense Formatting Functions. Today, we will see the Qlik Sense String Functions Tutorial. The Qlik Sense String Functions are applied to the strings or text values used in data load scripts. We use these String Functions of Qlik Sense to manage and handle the string values. Qlik Sense comes with a number of string functions which are important for us to understand.
So, let’s start Qlik Sense String Functions.
Recommended Reading – Qlik Sense Interpretation Function
2. Qlik Sense String Functions
Following are the Qlik Sense String Functions, let’s discuss them in detail –
i. Capitalize
The capitalize function returns a string with each word of the string starting with a capital letter.
The syntax for Capitalize Qlik Sense String Functions:
Capitalize(text)
For instance,
Capitalize(‘lesson string functions’) returns the string ‘Lesson String Functions’.
ii. Chr
The chr function returns the Unicode character corresponding to the integer you have entered in the expression.
The syntax for chr Qlik Sense String Functions:
Chr(int)
For instance, in the expression given below, the chr() function will return the alphabet corresponding to the integer entered in the expression following the standard code (ASCII).
Chr(65) returns the letter ‘A’ as a string, or,
Chr(90) returns the letter ‘Z’ as a string.
iii. Evaluate
This function checks an input value in the expression for whether it can be evaluated in some or not. If it involves the evaluation of some kind then this function returns the value after evaluation. It returns the values in dual data format. If there is no evaluation in the expression then NULL is returned.
You must read – Qlik Sense Null Functions
The syntax for Evaluate Qlik Sense String Functions :
Evaluate(expression_text)
For instance,
Evaluate( 5 * 8 ) will return the value ‘40’. Or,
Evalute(600/10) will return the value ’60’.
iv. FindOneOf
The findoneof() function returns the position of the characters attained as search result which it encounters while searching for a set of characters.
The syntax for FindOneOf Qlik Sense String Functions:
FindOneOf(text, char_set[, count])
Where, text is the text string in which you want to search the character set.
char_set is the set of characters which you want to be searched.
count is for counting the position number or occurrence of a certain character from the character set.
For instance,
FindOneOf(‘my example text string’, ‘et%s’, 3) will return the value ’12’ because the search is for any of the characters: e, t, % or s, and the character “t” is the third occurrence, and is in position 12.
v. Hash128
This function returns a 128-bit hash value corresponding to the value or string entered in the expression. The value usually returned is a 22-character long string value which is a combined 128-bit version of all the values that you have entered.
Get a deeper knowledge of Qlik Sense Mapping Functions
The syntax of Qlik Sense Hash128 String Functions:
Hash128(expr{, expression})
For instance, the expressions given below, will return a 128-bit hash value combining all the individual strings and store it in the system.
- Hash128 (‘abc’, ‘xyz’, ‘123’)
- Hash128 (Region, Year, Month)
vi. Hash160
This function returns a 160-bit hash value for the combined values corresponding to the string you enter in the expression. The result is usually a 27-character string which is stored as a 160-bit hash value in the memory.
The syntax of Hash160 Qlik Sense String Functions:
Hash160(expr{, expression})
For instance, a 160-bit hash value will return (combined) for all the strings entered separately in the expression as given below.
- Hash128 (‘abc’, ‘xyz’, ‘123’)
- Hash128 (Region, Year, Month)
vii. Hash256
This function returns a 256-bit hash value for the combined values corresponding to the string you enter in the expression. The result is usually a 43-character string which is stored as a 256-bit hash value in the memory.
We recommend you to read Qlik Sense Financial Functions
The syntax of Hash 256 Qlik Sense String Functions :
Hash256(expr{, expression})
For instance, a 256-bit hash value will return (combined) for all the strings entered separately in the expression as given below.
- Hash128 (‘abc’, ‘xyz’, ‘123’)
- Hash128 (Region, Year, Month)
viii. Index
The index() function is used to find the nth position of the occurrence of a character in a string. The string in which the position will be searched is mentioned in the expression as given below.
The syntax of index Qlik Sense string Functions :
Index(text, substring[, count])
Where, text is the main or the original string that contains the substring which is to be searched.
substring is the string which we want to search.
count is the specific number of occurrences which we can mention while searching. The count is specified by n and if no value of count is specified then 1 is considered by the system. The characters in the string are numbered 1 to n (right to left). If you use a negative number to specify the count, then the string is read from left to right.
You must take a look of Qlik Sense Counter Functions
For instance,
Index(‘abcdefghi’, ‘de’) will return 4 because ‘de’ substring appears on the 4th position in the string.
Index(‘abcdabcd’, ‘b’, 2) will return 6 because it is the position of the second occurrence of ‘b’.
Index(‘abcdabcd’, ‘b’,-2) will return 2 as it is the second occurrence of ‘b’ starting from the left end of the string.
ix. KeepChar
The KeepChar function returns a string which keeps only a particular section or some characters of the entire string. Which is why, the name of this function is ‘keepchar’ which indicates keeping a character.
The syntax of Qlik Sense KeepChar string Functions:
KeepChar(text, keep_chars)
Where, text is the original text string from which you can keep a set of characters.
keep_chars is the selected set of characters which you want the function to return. If you do not mention the keep_char parameter, then the original text string is returned.
For instance,
KeepChar (‘x1y2z3′,’123’) returns ‘123’.
KeepChar (‘x1y2z3′,’1234’) returns ‘123’.
KeepChar (‘x1y22z3′,’1234’) returns ‘1223’.
KeepChar (‘x1y2z3′,’312’) returns ‘123’.
x. Left
The left function returns a set of characters from the left of the string. Number of characters which you want the function to return can also be mentioned in the expression.
The syntax for Left Qlik Sense String Functions:
Left(text, count)
Where, text is the string which you enter.
count is the number of characters which you want to include in the resultant string from left.
Get the idea of how logical functions work in Qlik Sense
For example,
Left(‘abcdef’, 3) will returns ‘abc’.
xi. Len
This function returns the length of the input string, i.e. the total number of characters used in a string.
The syntax for Len Qlik Sense String Functions:
Len(text)
For example,
Len(‘QLIKSENSE’) will return ‘9’ as the length of the string.
xii. Lower
This function returns a string which is converted fully into a string having characters in lowercase.
The syntax for Lower Qlik Sense String Functions:
Lower(text)
For example,
Lower(‘QlikSense’) will return ‘qliksense’.
xiii. LTrim
This function trims the string of any leading paces between the characters or at the start or from the left of the string.
The syntax for LTrim Qlik Sense String Functions:
LTrim(text)
For example,
- LTrim( ‘ DataFlair’ )will return a trimmed string as, ‘DataFlair’.
- LTrim( ‘DataFlair ‘ )returns a string trimmed of spaces as ‘DataFlair’.
Recommended Reading – Qlik Sense Integer Functions
xiv. Mid
This function takes in three arguments, i.e. a text input, a start and a count. These parameters decide the length of characters from the given text input which should be returned as resultant string. The characters in the string are numbered from 1 to n (for n number of characters in the string).
The syntax for Mid Qlik Sense String Functions:
Mid(text, start[, count])
Where, text is the input string which you enter.
start marks the starting character from the string.
count decides up to which character the string should be taken. If you do not mention this parameter, then the string is taken from the start character to the last character of the string.
For example,
Mid(‘QlikSense’,5 ) will return ‘Sense’.
Mid(‘QlikSense’,1, 4 ) will return ‘Qlik’.
xv. Ord
This function returns the Unicode codepoint number for the first character of the string you input.
The syntax for Ord Qlik Sense String Functions:
Ord(text)
For instance, the expressions given below will return the integer value of the
Ord(‘A’) returns the integer 65 which is the Unicode value for the letter A.
Ord(‘QlikSense’) returns the integer 81 as it is the Unicode codepoint number for the letter Q.
Similarly, you can get any value in return for a letter from A (Unicode value 65) to Z (Unicode value 90).
xvi. PurgeChar
We use this function to remove the unwanted characters from a string as mentioned in the expression.
Take a tour to Qlik Sense Table Functions
The syntax for PurgeChar Qlik Sense String Functions:
PurgeChar(text, remove_chars)
Where text is the original string and remove_chars is the characters you want to remove from the strings.
For instance, PurgeChar(‘x1y2z3′,’123’) will return ‘xyz’ removing characters ‘123’ as mentioned in the second argument.
PurgeChar (‘a1b2c3′,’312’) will return ‘abc’ omitting characters 1,2 and 3.
xvii. Repeat
The repeat function repeats a character or all the characters in the string as many times as mentioned in the second argument of the function.
The syntax for Repeat Qlik Sense String Functions:
repeat(text[, repeat_count])
Where, text is the original string or a character that you want to repeat.
repeat_count is the parameter which sets how many times you want the character to be repeated.
For example, the expression given below returns the number of stars as per the integer mentioned in the value
Repeat( ‘ * ‘, rating ) Â will return ‘*****’ if the value of the variable rating=5.
xviii. Replace
This function replaces a set of characters in the string with other characters. This function reads the string from left to right and returns a value of string data type.
Learn the most important Qlik Sense Field Functions
The syntax for Replace Qlik Sense String Functions:
replace(text, from_str, to_str)
Where, text is the original string.
from_str is the character set which you want to replace.
to_str is the character set which you want to put in place of the chosen character set.
For example,
Replace(‘QlikView’,’View’,’Sense’) this will return ‘QlikSense’.
xix. Right
This function returns the characters from the right most corner of the string. You can also specify the number of characters that you want to include from the right.
The syntax for Right Qlik Sense String Functions:
right(text, count)
Where, text is the original string that will be input.
count is the number of characters that you want to include starting from the right most.
For example,
Right(‘QlikSense’, 5) will return ‘Sense’ which is the set of 5 characters starting from right.
xx. RTrim
The RTrim() function will trim a string entered in the script for any trailing spaces i.e. from the right of the string. Â
The syntax for RTrim Qlik Sense String Functions:
RTrim(text)
Where, text is the original string which you want to trim of any unwanted spaces.
For example,
RTrim( ‘Hello ‘ ) will return a trimmed string from the right, ‘Hello’.
xxi. SubField
We use the SubField function to create a new field using the data from old or already existing fields. Thus, the new field created is called the subfield. A subfield is said to have parts of the original field. Like a ‘City’ subfield from an original field ‘Address’, will keep all the cities in one field.
Recommended Reading – Qlik Sense Trigonometric Functions
The syntax for SubField Qlik Sense String Functions:
SubField(text, delimiter[, field_no ])
Where, text is the parent string or the parent field which contains the values separated by delimiters.
delimiter is the separating symbol used to separate two data values in the parent string.
field_no is the specific number of values within the parent string that you want to take and create a subfield for.
For instance, notice the code given below,
Name: LOAD * inline [ FullName 'Karan Arora' 'Anil Sharma' ‘Vikram Shah’ ‘Arti Mathur’ ]; SepNames: Load Name, SubField(Name, ' ',1) as FirstName, SubField(Name, ' ',-1) as Surname Resident Name; Drop Table Name;
This will create two new subfields named ‘FirstName’ and ‘Surname’ in the table called ‘SepNames’.
FullName | FirstName | Surname |
Karan Arora | Karan | Arora |
Anil Sharma | Anil | Sharma |
Vikram Shah | Vikram | Shah |
Arti Mathur | Arti | Mathur |
xxii. SubStringCount
This function counts how many times a substring i.e. a part of the original string is occurring the original or parent string. This function returns the answer as an integer value.
The syntax for SubString Qlik Sense String Functions:
SubStringCount(text, sub_string)
Where, text is the original string which is input in the script.
sub_string is the set of characters that you want to count the number of occurrences of.
For example,
- SubStringCount (‘abcdefgcdxyz’, ‘cd’) will return ‘2’ because ‘cd’ appears two times in the parent string.
- SubStringCount (‘abcdefgcdxyz’, ‘dc’) returns ‘0’ because there no such substring as ‘dc’ in the parent string.
You must read Qlik Sense Mathematical Functions
xxiii. TextBetween
The TextBetween() function returns the text or string enclosed between two delimiters.
The syntax for TextBetween Qlik Sense String Functions:
TextBetween(text, delimiter1, delimiter2[, n])
Where, text is the original string which is entered in the script.
delimiter1 is the first delimiter that encloses the desired set of characters.
delimiter2 is the second delimiter. First and the second delimiter will enclose the text you want the function to return.
n is the number of delimiter pair, the text enclosed by which you want to get as a result.
For example,
- TextBetween(‘<xyz>’, ‘<‘, ‘>’) will return ‘xyz’.
- TextBetween(‘<Qlik><Sense>’, ‘<‘, ‘>’,2) will return ‘Sense’ because it lies in the second pair of delimiters.
xxiv. Trim
The trim function trims a string for both leading (from left) and trailing (from right) spaces.
The syntax for Trim Qlik Sense String Functions:
Trim(text)
For example,
- Trim(‘ xyz’) trims the leading space and returns ‘xyz’.
- Trim(‘xyz ‘) trims the trailing space and returns ‘xyz’.
- Trim(‘ xyz ‘) trims both the leading and trailing spaces and returns ‘xyz’.
xxv. Upper
Just as opposite to the lower() function, this function coverts all the characters in the string into uppercase or capital letters.
The syntax for Upper Qlik Sense String Functions:
Upper(text)
For instance,
Upper(‘qliksense’) will return ‘QLIKSENSE’.
So, this was all in Qlik Sense String Functions. Hope you like our explanation.
3. Conclusion
Hence, these were all the Qlik Sense String functions which are applied on a string when the strings are used in a script. We hope the explanations given in this lesson were helpful enough for you to start using these functions into your data load script of Qlik Sense.
Still, if you have any query regarding Qlik Sense String Functions, ask in the comment tab.
See also –Â
Your opinion matters
Please write your valuable feedback about DataFlair on Google