Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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,527 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,527 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,688 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,688 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,688 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,688 Views
blaise
Partner - Specialist
Partner - Specialist

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

0 Likes
3,688 Views
isingh30
Specialist
Specialist

Thank you

0 Likes
3,688 Views
Miles_Dyson
Contributor II
Contributor II

Thank you in 2021

396 Views