transform field to get sum of values and absolute values

[tomovangel]

Hello guys,

I am experiencing a little confusion, with certain fields.

In the attached excel file, you will find the test transactional data of a company I am working with.

I am bashing my head against transforming the field SalePrice, from this format "23,45 leva." to 23,45 . Leva is the currency of my country.

So Far I have tried with 
KeepChar(SalePrice ,'.,01234567890'), but this doesn't work correctly, it doesnt turn all values and it points to a . in the end. 

I also have a problem with converting the Quantity field from - to +, I have tried with :
Fabs(Quantity), but this returns 0 value ..

How can I transform the SalePrice field, so I only get the Price in an acceptable format with the cents behind the coma. 

Thanks dear Qlikers !

[tomovangel]

Okay, it is working on the example file that I provided, But I am having trouble applying it to the whole document,

It  returns different values..

Do you want me to post the whole document? Its 9300 records in excel..

[sunny_talwar]

You can if you are okay with it... but make sure your environmental settings are like this

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

[tomovangel]

Okay, Thanks Sunny, this works perfectly, i Just had to assign the correct format for my database settings  !!!

[tomovangel]

Thanks Nallani, this works good, I haven't thought of using the Replace command, thanks

[sunny_talwar]

Awesome

[tomovangel]

I must try to understand more correctly len and trim, because the way I see it, this 2 formulas are pretty good and can be used in numerous different options

[sunny_talwar]

Yes, they are useful functions to know about

[manoj217]

purgechar(SalePrice,'leva')