topic Re: NUM Probelm in QlikView

NUM Probelm

fkeuroglian — Thu, 20 Feb 2014 20:41:55 GMT

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

Re: NUM Probelm

stevedark — Thu, 20 Feb 2014 21:17:18 GMT

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

Re: NUM Probelm

Anonymous — Thu, 20 Feb 2014 21:23:47 GMT

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

Re: Re: NUM Probelm

fkeuroglian — Thu, 20 Feb 2014 22:42:05 GMT

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

Re: NUM Probelm

Anonymous — Thu, 20 Feb 2014 22:48:13 GMT

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

Re: NUM Probelm

stevedark — Thu, 20 Feb 2014 22:52:09 GMT

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

Re: Re: NUM Probelm

fkeuroglian — Thu, 20 Feb 2014 22:57:15 GMT

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

Re: NUM Probelm

Anonymous — Thu, 20 Feb 2014 23:17:36 GMT

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?

Re: NUM Probelm

fkeuroglian — Thu, 20 Feb 2014 23:20:51 GMT

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

Re: NUM Probelm

Anonymous — Thu, 20 Feb 2014 23:24:44 GMT

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

Re: NUM Probelm

fkeuroglian — Thu, 20 Feb 2014 23:31:30 GMT

I do not why, this is the problem i have!

i dont understand it

Re: NUM Probelm

Anonymous — Thu, 20 Feb 2014 23:41:28 GMT

Fernando,

The issue is with the data, not Qlikview, so I would suggest looking at the source data. If it is correct in the source, perhaps in the .qvw generating this QVD the decimal separator is defined as "." instead of ","

Regards,
Sean

Re: NUM Probelm

fkeuroglian — Fri, 21 Feb 2014 01:45:50 GMT

Sean

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='$U #.##0,00;($U #.##0,00)';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

Set Path_QVD='../QVD/';

Set Path_Recursos='../Recursos/';

it is strange,not?

Re: NUM Probelm

Anonymous — Fri, 21 Feb 2014 02:04:45 GMT

Fernando-

Can you query the source table (ZRM07MLBS) and confirm the correct value is stored there? I am guessing that it is stored incorrectly as 80.000 in your source database.

Regards,
Sean

Re: NUM Probelm

Anonymous — Fri, 21 Feb 2014 06:13:41 GMT

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

Re: NUM Probelm

fkeuroglian — Fri, 21 Feb 2014 11:57:50 GMT

Steven, Sean and Walid

Thank you for all your support and help, it was a problem with the source and the solution i have to implement is transform all the data before create the qvd, i transformate all after create the qvd, but if i do it befero works and transform ok!

thank you a lot!

Fernando

Re: NUM Probelm

stevedark — Fri, 21 Feb 2014 12:01:59 GMT

Glad it's now working.