Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
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
Hi DataNibbler,
In excel I have in money format as I shown in Price column,
In qlik is getting like MyOutput column.
Hi ,
go to number format
And remove $ if you use Money
May be KeepChar()
Num#(KeepChar(Price, '0123456789-'), '#,##0.00;-#,##0.00') as Price
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 ','$','') )
or this:
Num(Money#(Price, '$#,##0.00;-$#,##0.00')) as Price
Hi Bill,
I cannot use =trim(replace('$-10 ','$','') ) because i have so many records.
thank you
Hi Sunny, thanks for the reply,
Both are not working .
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!