Skip to main content

Design

The Design blog is all about product and Qlik solutions, such as scripting, data modeling, visual design, extensions, best practices, and more!

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
Jennell_McIntire
Employee
Employee

Do you even need to delete or keep some characters in a string field?  The PurgeChar and KeepChar functions allow you to purge and keep characters that are in a string.  The PurgeChar function takes two parameters.  The first is the string and the second is the character(s) that are to be removed from the string.  The KeepChar function also takes two parameters but in this case the second parameter is the character(s) that are to be kept in the string.  Let’s take a look at some examples.

 

Sometimes you may have a dataset that has garbage in it like in the FirstName field below.

FirstName.png

In this case there are characters after each name that I do not need.  In order to remove these characters from the field, I can use the PurgeChar function in my script (see below) to remove all the unwanted characters from the FirstName field.

Purge script.png

Once I run the script the names look like this:

FirstName Clean.png

The KeepChar function works similar except in this function you indicate what characters you would like to keep.  This may be helpful when you have field that includes a mix of numbers and letters but you only want to keep the numbers or the letters.  In this example, I have a ProductCode field that has codes that are made up of numbers and letters but I only want the numeric data.

ProductCode.png

In my script, I can use the KeepPurge function and use the second parameter to list all the numbers since those are the characters I want to keep in the string.

Keep script.png

The end result looks like the image below.  The product codes are now all numeric and the letters have been removed.

ProductCode Clean.png

There are a host of string functions that can be used to clean up or modify a string but when there are specific characters that you need to remove or keep, PurgeChar and KeepChar can be helpful and easy to add to your script or chart expression.  These functions work well when you need to remove or keep ALL references to a character in a string.

 

Thanks,

Jennell

19 Comments
Not applicable

Very Simple

Thanks

0 Likes
3,355 Views
Not applicable

Is this available for use with a SQL Server extract?  I can't get it to work.  I get an error message saying "ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'KeepChar' is not a recognized built-in function name."

I can't find any way of keeping the exact same string value, including all special characters in numbers, in the import.

0 Likes
3,355 Views
Anil_Babu_Samineni

No, SQL engine works only there functionality and Qlik engine works only Qlik functionalities. You must load the data from SQL and then you can call up in Load statement rather SELECT statement

3,516 Views
anderseriksson
Partner - Specialist
Partner - Specialist

You are trying to use KeepChar in the SQL-statement but that is sent to the SQL Server and only statements recognized by SQL Server is allowed there as it is the database engine executing that.

Stack a LOAD-statement on top of the SQL-statement and you can use all Qlik-statements in that.

3,516 Views
greg066
Contributor
Contributor

Nice solution and instructions. Does anyone know if QlikView has shorter way of executing in the script the following:

- Remove all numeric from the field (thus keeping only characters)

- Remove all letters/characters from the field (thus keeping only numeric)

It seems like having to type '0123456789' in your script is a little tedious and I haven't found the equivalent for letters. Typing a-z would not be that efficient.

Thanks

0 Likes
3,516 Views
anderseriksson
Partner - Specialist
Partner - Specialist

Here you go, copy/paste and change the field name;

To remove all numeric values from the field: PurgeChar([field], '1234567890');

To remove all letters/characters from the field only keeping numeric: KeepChar([field], '1234567890');


Typing '1234567890' tedious? Come on!

3,516 Views
blaise
Partner - Specialist
Partner - Specialist

Just place your a-z in a variable and use that one in your keepchar().

0 Likes
3,516 Views
isingh30
Specialist
Specialist

Thank you

0 Likes
3,516 Views
Miles_Dyson
Contributor II
Contributor II

Thank you in 2021

224 Views