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 !

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD Date,

    Type,

    Document,

    [Serial Number],

    DeliveryPrice,

    Quantity,

    Num(Num#(Left(SalePrice, Len(SalePrice)-5), '##.##', '.', ',')) as SalePrice

FROM [..\..\Downloads\Results.xlsx]

(ooxml, embedded labels, table is Sheet1);

[tomovangel]

Nope, this is not correct.

If you can see in your example where there is some cents into the number, for example 80.40 it transformed it to 89.

Any other solution?

I have tried almost everything I found on the Community..

[ChennaiahNallani]

try like below

LOAD

    "Date",

    "Type",

    Document,

    "Serial Number",

    DeliveryPrice,

    Quantity,

    Replace(Quantity,'-','+') as Quantity1,

    SalePrice,

     Replace(SalePrice,'leva.','') as Sa as SalePrice1

FROM [lib://c/Results.xlsx]

[sunny_talwar]

What is the Document where the SalePrice is 80.4? I am not sure if I can find this value...

[tomovangel]

Yes, its 2918668

[tomovangel]

Nope, this returns just the values, for example if i have 25.4 it will return 254 ..

[tomovangel]

O wait, i have been looking at the wrong numbers, just give me a sec to clarify it

[sunny_talwar]

I transformed SalePrice and not the DeliveryPrice... What are you looking to transform Sale or Delivery?

[ChennaiahNallani]

Replace(SalePrice,'leva.','') as  SalePrice