Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner - Specialist
Partner - Specialist

transform field to get sum of values and absolute values

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 !

1 Solution

Accepted Solutions
sunny_talwar

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);

View solution in original post

17 Replies
sunny_talwar

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
Partner - Specialist
Partner - Specialist
Author

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
Creator III
Creator III

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...

Capture.PNG

tomovangel
Partner - Specialist
Partner - Specialist
Author

Yes, its 2918668 2918668.png

tomovangel
Partner - Specialist
Partner - Specialist
Author

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

tomovangel
Partner - Specialist
Partner - Specialist
Author

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?

Capture.PNG

ChennaiahNallani
Creator III
Creator III

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