topic Re: Remove characters from a string. in QlikView

Remove characters from a string.

Fri, 20 Nov 2020 18:02:30 GMT

i have a field download having values 111.19kbps , 790kbps and many more.

i want the field to contain only values such as 111.19 , 790 and remove the 'kbps from the end'

i tried using

load

....

KeepChar(Download,'0123456789') as download

....

from ......

as well as

load

....

left(download,3) as download.

....

from ......

but none of those worked

any help would be highly appreciated.

Thanking you guys in advance.

Re: Remove characters from a string.

francoiscave — Mon, 26 Jan 2015 19:36:28 GMT

Hi Tausif,

Use the Replace() function. It will be easier.

Replace(Download,'kbps','')

A right() function could be useful too...

François

Re: Remove characters from a string.

maxgro — Mon, 26 Jan 2015 19:44:59 GMT

keepchar(Download, '.0123456789')

Re: Remove characters from a string.

Mon, 26 Jan 2015 19:51:38 GMT

i tried but when i reload it and after displaying the field in list box it is empty.

Replace(Download,'kbps','') as download

Re: Remove characters from a string.

Colin-Albert — Mon, 26 Jan 2015 19:58:18 GMT

Your keepchar expression has removed the decimal point. Add the decimal point to the character list.

Keepchar(Download, '0123456789.') as download

The simplest way to test this is to create a Straight Table chart with Download as the dimension and the keepchar example ression as the chart expression. You can then test the expression without the need to reload the data. Once the expression works, copy it to your script.

Re: Remove characters from a string.

MarcoWedel — Mon, 26 Jan 2015 23:53:28 GMT

Hi,

you could also load this field as numerical value and still keep the physical unit:

Num#(bitratetxt,'#,###.#kbps') as bitrate

Purging the unit could be done like

Num(Num#(bitratetxt,'#,###.#kbps')) as bitrate2

on the other hand.

table1:
LOAD bitratetxt,
     Num#(bitratetxt,'#,###.#kbps') as bitrate,
     Num(Num#(bitratetxt,'#,###.#kbps')) as bitrate2,
     RecNo() as ID     
Inline [
bitratetxt
111.19kbps
790kbps
1234kbps
1.123kbps
11.23kbps
112.3kbps
0.11234kbps
3456789kbps
3.456789kbps
34.56789kbps
345.6789kbps
3456.789kbps
34567.89kbps
345678.9kbps
];

hope this helps

regards

Marco