Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
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
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
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
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
What is it that denotes that 80.000 should be 80 whilst 3.445,51 should be 3445?
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??
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?
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
To rephrase, why should qlikview treat "." differently in 80.000 and 3.445,51 ?