Strings functions
Add padding
Adds one or more occurrence of a character at the beginning or the end of the content of the cells.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Repeat count | Number of times the chosen character will be added to the content of the cell. |
Add character | Enter the character you want to add to the content of the cell as padding. |
Position |
Select where you want to add the padding character.
|
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Configuration | Output |
---|---|---|
TableRecipe |
|
11111TableRecipe |
Calculate length
Calculates the number of characters from each cell of the column and outputs the result in new column.
A new column containing the result of the function will be created for each of the selected column. The default name of the new columns will be <original_column_name>_length.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Example
Input | Output |
---|---|
TableRecipe | 11 |
Change to lower case
Converts all the text from the column cells to lower case.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Output |
---|---|
Table Recipe | table recipe |
Change to title case
Converts all the text from the column cells to title case.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Output |
---|---|
table recipe | Table Recipe |
Change to upper case
Converts all the text from the column cells to upper case.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Output |
---|---|
Table Recipe | TABLE RECIPE |
Concatenate columns
Merges the content of a column with another one or a fixed value.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Prefix | Optionally add a character or string at the beginning of the merged data. |
Use with |
|
Separator | Input any character to act as separator for the merged data. |
Add separator | Select the condition on which the separator will be applied. |
Suffix | Optionally add a character or string at the end of the merged data. |
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Configuration | Output |
---|---|---|
|
|
|
|
|
|
Contains text
Checks if the cells contain a specific value. The function will return TRUE if the selected text is found within the cell, otherwise it will return FALSE. This function is case sensitive.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Use with |
|
Example
Input | Configuration | Output |
---|---|---|
|
|
|
Convert to text
Converts the type of strings contained in the column so that they are interpreted as text in the system.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Extract a value by index
Extracts a substring selected using index positions, to a new column.
An index represents a number of characters from which or to which the selection applies. Index 0 corresponds to the first character.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
From |
Select how you want to start the value selection:
|
To |
Select how you want to end the value selection:
|
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Configuration | Output |
---|---|---|
TableRecipe |
|
Table |
QlikCloudAnalytics |
|
Cloud |
Match pattern
Checks if the cell is similar to a specific pattern. The function will return TRUE if the selected pattern is found within the cell, otherwise it will return FALSE.
A new column containing the result of the function will be created for each of the selected column. The default name of the new columns will be <original_column_name>_matches.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Pattern |
Select one of the predefined regex patterns, or another combination of operator and value:
|
Operator |
If you have selected Other as Pattern, you can select one of the following operator, and set you custom value in the following Value field:
|
Example
Input | Configuration | Output |
---|---|---|
|
|
|
|
|
|
Match similar text
Checks if the cell is similar to a specific value. The function will return TRUE if the selected text is found within the cell, otherwise it will return FALSE.
A new column containing the result of the function will be created for each of the selected column. The default name of the new columns will be <original_column_name>_matches.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Use with |
|
Fuziness | Set a number that corresponds to the Levenshtein distance you want to use for the check. The Levenshtein distance represents the number of edits you need to make in order to transform a string into another. |
Example
Input | Configuration | Output |
---|---|---|
|
|
|
Remove non alpha numeric characters
Removes all characters that are not 0-9, Aa-Zz, and accented letter from the content of a cell. The following character, as well as whitespaces, are kept: 1234567890abcdefghijklmnopqrstuvwxyzáàâäãåæçéèêëíìîïñóòôöõøœßúùûüABCDEFGHIJKLMNOPQRSTUVWXYZÁÀÂÄÃÅÆÇÉÈÊËÍÌÎÏÑÓÒÔÖÕØŒSSÚÙÛÜ.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Output |
---|---|
Q=+L**€IK#$ 12%3,4 |
QLIK 1234 |
Remove non numeric characters
Remove all characters that are not 0-9. Decimal separators and whitespaces are also kept.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Output |
---|---|
Q=+L**€IK#$12%3,4 |
123,4 |
Remove part of text
Removes the specified strings from the cells in the column.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Operator |
Select the operator that will be used to match the text to remove:
|
Value | Enter the character or string to remove. |
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Configuration | Output |
---|---|---|
|
|
|
Remove trailing and leading character
Removes trailing and leading spaces or other specified characters.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Padding character |
|
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Output |
---|---|
[][]TableRecipe[] |
TableRecipe |
Search and replace
Replaces cells or parts of cells which contain a specific value with another one.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Operator |
Select the operator that will be used to match the text to search:
|
Value | Enter the character or string to search. |
Replace with | Enter the value with which to replace the specified strings. |
Overwrite entire cell | Select this checkbox if you want the resulting cell to only contain the value used in the Replace with field. |
Create new column | Select this check box if you want to output the result of this function in a new column and keep the original untouched. |
Example
Input | Configuration | Output |
---|---|---|
|
|
|
Split column
Splits the content of a cell in several columns, based on a separator.
Properties
Property | Configuration |
---|---|
Columns to process |
Select the column or columns on which you want to apply the function. If you want to apply it on more than one column, use the drop-down list to select which columns, and click Apply. |
Parts |
Enter the number of columns you want to create. If the original string contains more parts than what you have specified in the Parts field, the remaining string will be returned in the last created column. |
Separator |
From the drop-down list, select the character used as basis for the split:
|
Example
Input | Configuration | Output |
---|---|---|
Column A: 12/07/1998 |
|
|
Column A: 20-03-2025 |
|
|