topic Re: Extracting currecy Characters but keeping decimal separators in QlikView

Extracting currecy Characters but keeping decimal separators

dino_ferrarini — Wed, 25 Nov 2020 16:16:04 GMT

Hello Guys,

I am trying to created a Calculated Filed which extracts numbers and decimal separator from Fields with Currecy symbols.

Example Data

ARS10,952.23

S/.5,324.23

$1,234.34

Ch$3,452.12

I am trying to get

10,952.23

5,324.23

1,234.34

3,452.12

I am using the KeepChar Functions wich is supposed to allow multiple characters. The problem is that is not allowing me to keep numbers and characters las , or . or any other letter

What works: KeepChar([Order Value], 0123456789)

Not working

KeepChar([Order Value], ´0123456789´) or KeepChar([Order Value], ´.,0123456789´) or KeepChar([Order Value], ´$0123456789´)

The Function Char () works but allows just one symboy; for example KeepChar([Order Value], Char(44))

How should I addres this issue?

All the best,

Dino

Re: Extracting currecy Characters but keeping decimal separators

marcus_sommer — Mon, 17 Sep 2018 13:58:30 GMT

The following should work: KeepChar([Order Value], '.0123456789'). Further make sure that these values are recognized as numbers with the right delimiters - if not you might need an additionally num#() with your needed format-string.

- Marcus

Re: Extracting currecy Characters but keeping decimal separators

Anil_Babu_Samineni — Mon, 17 Sep 2018 14:09:25 GMT

Try this?

Num(Num#(PurgeChar([Order Value],'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz$/.,')))/100

Re: Extracting currecy Characters but keeping decimal separators

cwolf — Mon, 17 Sep 2018 14:23:18 GMT

use KeepChar to determine the first position of a number in the string:

TEST:

LOAD

Mid([Order Value],Index([Order Value],Left(KeepChar([Order Value],'0123456789'),1))) as [Order Value]

;

LOAD * INLINE [

Order Value

'ARS10,952.23'

'S/.5,324.23'

'$1,234.34'

'Ch$3,452.12'

];

- Christian

Re: Extracting currecy Characters but keeping decimal separators

anders_thorngaa — Mon, 17 Sep 2018 14:58:14 GMT

Based on some of the very helpful answers mentioned above to your problem, I tried the following which worked for me.

Data_tmp:

LOAD * INLINE [

Order value

"ARS10,952.23"

"S/.5,324.23"

"$1,234.34"

"Ch$3,452.12"

];

Data:

LOAD

KeepChar([Order value],'0123456789')/100 as CurrencyVal

Resident Data_tmp;

DROP Table Data_tmp