how to remove $ in money column?

[#1]

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.

[datanibbler]

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

[#3]

Hi DataNibbler,

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

In qlik is getting like MyOutput column.

[#4]

Hi ,

go to number format

And remove $ if you use Money

[sunny_talwar]

May be KeepChar()

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

[Anonymous]

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 ','$','') )

[sunny_talwar]

or this:

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

[#8]

Hi Bill,

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

thank you

[#9]

Hi Sunny, thanks for the reply,

Both are not working .

[jonas_rezende · Accepted solution]

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!