Solved: NUM Probelm - Qlik Community

fkeuroglian · ‎2014-02-20

Hi, i have a qvw and have problem with num

i bring info from a qvd and i try to put number format to sum but i have problems

please help, if anybody can!

thank you!

i atttached a qvw wit the 2 examples numbers that i have problem,

Field0_Material= 224097001 OR Field0_Material= 222290;

correct is:

224097001 =80

222290=3455,51

i have to sum , in each is correct one and wrong the other

please help with this format!

thank you!

Fernando

Anonymous · ‎2014-02-21

use floor() function to remove the decimal places from 3.445,51 to 3.445 floor(Num(Value,'#.##0')) and use this format in the num function to achieve the desired format Num(Value,'#.##0')

View solution in original post

stevedark · ‎2014-02-20

224097001 is 132 because of the other values in the table. If you put a straight table on the page with all values you will see these.

Your value of TEST3 seems to have the right value in it - though you should be able to achieve this with out the nested num# and replace values.

TEST2 is failing as you are not converting the value to a number (with num#) just trying to format it as a number with (with num).

StockLibreTEST has an incorrect format on it, so it is treating it as a string (which when summed gives zero).

I would have thought that this would give you a correct value:

num(num#([Field5_Libre utiliz.] , '#.##0,00'), '#.##0,00') as [Numeric Value],

Without access to your QVD I can not test this though.

Note the difference between the num and the num# function, one which converts the other that formats - this is why they are nested.

Hope that helps.

Steve

Anonymous · ‎2014-02-20

Fernando,

You have correctly set the ThousandSep and DecimalSep variables, so it is strange that it is not loading 3.455,510 as a number. I would guess there are hidden characters, try using num(trim([Field5_Libre utiliz.]))as TEST2 in your load statement.

It also seems like there is some inconsistency in your data. When you load the field without a number format, the values are 80.000 and 3.455,510.

If the correct values are 80 and 3445,51, then you want Qlikview to convert 80.000 to 80 (eighty NOT eight thousand) and keep 3.445,51 as it is loaded, is that correct?

Regards,

Sean

fkeuroglian · ‎2014-02-20

Steve, Sean thank you for your fast answer

but i cant solve, its rare because dont match!

i give you the qvw again with yours formula and the qvd too

thank you in advance again!

Fernando

Anonymous · ‎2014-02-20

Fernando,

Please see my second comment above about the inconsistent source data.

It appears that adding trim() did work, but that the issue you're facing now is 80.000 should be 80, where as 3.445,51 should be 3.445,51.

Sean

stevedark · ‎2014-02-20

What is it that denotes that 80.000 should be 80 whilst 3.445,51 should be 3445?

fkeuroglian · ‎2014-02-20

Yes Sean , Steven

80.000 should be 80 and 3.445,51 should be 3445, but o dont know what happend, if you se the original(without transform nothing) fileld 80 is ok but 3445,51 is wrong.

what could be??

Anonymous · ‎2014-02-20

Fernando,

The unformatted values are 80.000 and 3.445,51, so if you want 80.000 to be 80 but 3.445 to be 3.445 then you will need to add some additional logic. e.g.:

if(trim([Field5_Libre utiliz.]) = '80.000',80,

num(trim([Field5_Libre utiliz.]))) as Field

As Steve asked already- what is it that denotes that 80.000 should be 80 whilst 3.445,51 should be 3445?

fkeuroglian · ‎2014-02-20

Sean, it meens that the original value in the sap report that i have to extract is 80 and not 80000, and the other original value is 3445,51

but i cant get to these numbers

Anonymous · ‎2014-02-20

To rephrase, why should qlikview treat "." differently in 80.000 and 3.445,51 ?