Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

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

Re: transform field to get sum of values and absolute values

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

17 Replies

Re: transform field to get sum of values and absolute values

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

Partner
Partner

Re: transform field to get sum of values and absolute values

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

Re: transform field to get sum of values and absolute values

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]

Re: transform field to get sum of values and absolute values

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

Capture.PNG

Partner
Partner

Re: transform field to get sum of values and absolute values

Yes, its 2918668 2918668.png

Partner
Partner

Re: transform field to get sum of values and absolute values

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

Partner
Partner

Re: transform field to get sum of values and absolute values

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

Re: transform field to get sum of values and absolute values

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

Capture.PNG

Highlighted
ChennaiahNallani
Contributor III

Re: transform field to get sum of values and absolute values

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