Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

how to remove $ in money column?

Hi all,

I have Price coulmn in excel sheetas below:

I used  trim(replace(Price),'$','') as Price1

Product              Price              My output          expected o/p

A                        $10                    10                         10

B                        $20                    20                          20

C                        $15                    15                         15

D                        $-10                    10                        -10

E                        $-5                      5                          -5

F                        $25                      25                        25

G                        $-30                    30                        -30

Thank you.

Tags (1)
1 Solution

Accepted Solutions
jonas_rezende
Valued Contributor

Re: how to remove $ in money column?

Hi, Prashanth Reddy.

Try

in script:

LOAD

Product,

Num(PurgeChar(Price,'$'))  as Price

FROM [<nametable>];


in Expression


Num(PurgeChar(Price,'$'))


P.S.: add expression of aggregation desired.


Hope this helps!

13 Replies
datanibbler
Esteemed Contributor

Re: how to remove $ in money column?

Hi,

what format is your price_column in QlikView (not in Excel)?

If it's a STRING, you can use any of the STRING functions like SUBFIELD().

HTH

Best regards,

DataNibbler

Not applicable

Re: how to remove $ in money column?

Hi DataNibbler,

In excel I have in money format as I shown in Price column,

In qlik is getting like MyOutput column.

Not applicable

Re: how to remove $ in money column?

Hi ,

go to number format

And remove $ if you use Money

Re: how to remove $ in money column?

May be KeepChar()

Num#(KeepChar(Price, '0123456789-'), '#,##0.00;-#,##0.00') as Price

Re: how to remove $ in money column?

I am surprised you get any output from your expression.  I have sorted your brackets, try this :

     =trim(replace(Price,'$','') )

I tried it like this it is ok :

     =trim(replace('$-10 ','$','') )

Re: how to remove $ in money column?

or this:

Num(Money#(Price, '$#,##0.00;-$#,##0.00')) as Price

Not applicable

Re: how to remove $ in money column?

Hi Bill,

I cannot   use  =trim(replace('$-10 ','$','') ) because i have so many records.

thank you

Not applicable

Re: how to remove $ in money column?

Hi Sunny, thanks for the reply,

Both are not working .

jonas_rezende
Valued Contributor

Re: how to remove $ in money column?

Hi, Prashanth Reddy.

Try

in script:

LOAD

Product,

Num(PurgeChar(Price,'$'))  as Price

FROM [<nametable>];


in Expression


Num(PurgeChar(Price,'$'))


P.S.: add expression of aggregation desired.


Hope this helps!

Community Browser