Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The Replace() function can be used in the script or a chart to replace all occurrences of a sub-string with another sub-string within an input string. Basically, you can replace a character or string of characters in a field or input string. The syntax from Qlik Help looks like this:
Replace(text, from_str, to_str)
The first parameter, text, is a string. This can be a field or some text. The second parameter, from_str, is the string you would like to replace in the input string. This can be a single character or a string of characters. The third parameter, to_str, is the string that should replace the second parameter. If the from_str is not found in the text then nothing is replaced. The Replace() function works from left to right.
I have used the Replace() function when I have a field that has some characters that I do not need. For example, I was loading names in a recent project and some of the names had a leading underscore character (_). Since this was the case with only some of the names and not all, I used the Replace() function to replace the underscore with an empty string using syntax like this:
You can also nest the Replace() function. I have used this if there is more than one character I would like to replace in a string. The nesting works from the inside out so keep this in mind as it may affect your results. In the table below are a few examples of how Replace() can be used.
- Column 1 - shows the original text string for Product. This is the first parameter for all the Replace() examples.
- Column 2 – replaces the underscore with a space.
- Column 3 – is an example of a nested Replace() function. First it replaces the underscore with an empty string. Then it looks at the result of that string and replaces the question mark with an empty string. The last step is to look at the results of that string and replace underscore question mark (_?) with a space (which will not be found in the Column 3 example).
- Column 4 – is another example of a nested Replace() function but notice how the order affects the results for the input string “Product_?E”. Since the first Replace() function to be evaluated replaces the “_?” with a space, the next two Replace() functions do not find the second parameter to replace. Therefore, the result is “Product E” with a space before “E.” In Column 3, the result is “ProductE,” without a space, since the underscore and the question mark were replaced individually with an empty string before the Replace(‘_?’, ‘ ‘) part of the expression was executed.
While my example shows how the Replace() function can be used in a chart expression, I always use it in the script to handle any replacements as I load the data. What is nice about the Replace() function is it does not modify the text if the sub-string cannot be found. With that in mind, you should make sure you want to replace all occurrences of the sub-string because there is not an option to pick and choose which sub-string occurrences to replace - all of them will be replaced.
Thanks,
Jennell