Solved: Trouble with decimal settings ... again - Qlik Community

datanibbler · ‎2015-06-29

Hi,

I have a value that I have calculated in my script as >> 24,93051 <<.

Script settings are:

>>>>>>>>>>>>>>

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';

<<<<<<<<<<<<<<<<<<<<<

So the comma should be used as decimal_separator - I know that QlikView calculates with the opposed settings internally. Right?

So when I type

>> round([that value], '0.0001' <<

should not that round the output to four decimals?

Well, it doesn't. Instead this gives me 0.0025 or something.

So what do I have to do to get this value rounded to four decimals?

Thanks a lot!

Best regards,

DataNibbler

datanibbler · ‎2015-06-30

Hi Avinash,

finally I think I have made it - though I don't quite understand.

I had a few variables, all numeric, all with the "comma" and a few values from tables to multiply with those, all numeric, all with the "comma" - still, at the end of it, I had two identical-looking values, but the IF_query told me they were not equal.

Now I did the same to the final values once again - I used first the Replace() (to swap the "comma" with the "dot") and then num# to get a valid numeric value again - and now the query tells me they are equal.

Eventually I have to find out what is the root cause of this strange happening - else I will have the same kind of trouble again and again. It must be connected to the fact that the basic settings in Excel are in German formatting - the "comma" as separator - I cannot change those, I have no admin_rights on my machine, so I have mirrored those in the SET commands in my script, but the internal calculations of QlikView are done the other way round.

Well, I guess this is solved. Thank you all!

View solution in original post

marcus_sommer · ‎2015-06-29

Hi DataNibbler,

you could use num() to format your number.

- Marcus

sunny_talwar · ‎2015-06-29

What you have seems to work for me:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

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

SET DateFormat='M/DD/YYYY';

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

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table:

LOAD *,

Round(Value, '0.0001') as NewValue;

LOAD * Inline [

Value

24,93051

] (delimiter is '|');

Output:

avinashelite · ‎2015-06-29

try like this:

num(value,'###0.0000')

rubenmarin · ‎2015-06-29

Hi DataNibbler,

If I use =Round('24,93051', 0.0001) ir returns 24,9305

Remove the single quotes in 0.0001 to check if it works.

datanibbler · ‎2015-06-29

Hi Avinash,

that gives me the strangest result of all - my value now is

>> 115,69281 <<

When I go like that with the num() function, what I get is

>> 116.##0,0000 <<

What is that supposed to be?

datanibbler · ‎2015-06-29

Hi Ruben,

do I have to put the value that I want to round in quotes?

I'll try that.

rubenmarin · ‎2015-06-29

Hi, not sure, if you're using the value as a variable maybe it's using the comma, in something like

Round(24,93051, 0.0001)

I used the single quote as delimiter for the number, without single quotes the comma used as decimal separator will be used as a parameter separator, wich can surely lead to some issue.

datanibbler · ‎2015-06-29

Hi Ruben,

that sounds logical. In fact I have had that issue elsewhere - that functions interpret the comma as the separator between different parameters.

The value is calculated in the LOAD statement. I use a variable (multiplied with a field from the table) - the variable has the dot as decimal_separator, but the field_value has the comma. That seems to cause the issue.

OK - when I insert a PRECEDING LOAD with a REPLACE() function, I can round it all right. let's see ...

sasiparupudi1 · ‎2015-06-29

Round(Num#(Value, '#,##0.#'),0.0001)