Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

PurgeChar and KeepChar Functions

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

18 Comments
prieper
Honored Contributor II

I am using a similar solution in reading textfiles, which are created by different persons in different countries. You encounter a different presentation of numbers ("," as decimal or thousand-separator, leading or trailing "-" or brackets to display negative numbers and currencies, also either trailing or leading in the same cell.

Thanks

Peter

256 Views
cksuperlatif
New Contributor II

Thank you much I used it despite regulare expression

0 Likes
256 Views
paul_scotchford
Valued Contributor

Nice hint, thanks for posting.

0 Likes
256 Views
nareshguntur
Valued Contributor

Thanks Jennell. Simple and important functions which people rarely use.

0 Likes
256 Views
ishanbhatt
Contributor II

Thanks a lot Jennell. It's help a lot.Smiley Happy

0 Likes
256 Views
Not applicable

Thanks, Nice one

0 Likes
256 Views
tiago_hubner
New Contributor III

Nice, thanks.

256 Views
sureshqv
Esteemed Contributor III

very nice,,

0 Likes
256 Views
hirishv7
Honored Contributor

Thanks a lot .

0 Likes
256 Views

Wav, Really nice and simple

0 Likes
256 Views
Not applicable

Very Simple

Thanks

0 Likes
256 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
256 Views

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

256 Views
anderseriksson
Contributor III

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.

256 Views
raidergregory
New 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
256 Views
anderseriksson
Contributor III

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!

256 Views
blaise
Contributor III

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

0 Likes
256 Views
isingh30
Contributor III

Thank you

0 Likes
256 Views