Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
dino_ferrarini
Contributor II
Contributor II

Extracting currecy Characters but keeping decimal separators

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

1 Solution

Accepted Solutions
anders_thorngaa
Contributor III
Contributor III

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

View solution in original post

4 Replies
marcus_sommer

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

Anil_Babu_Samineni

Try this?

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
cwolf
Creator III
Creator III

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

anders_thorngaa
Contributor III
Contributor III

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