topic Re: Trouble with decimal settings ... again in QlikView

Trouble with decimal settings ... again

datanibbler — Mon, 29 Jun 2015 12:47:36 GMT

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

Re: Trouble with decimal settings ... again

marcus_sommer — Mon, 29 Jun 2015 12:51:08 GMT

Hi DataNibbler,

you could use num() to format your number.

- Marcus

Re: Trouble with decimal settings ... again

sunny_talwar — Mon, 29 Jun 2015 12:54:16 GMT

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:

Re: Trouble with decimal settings ... again

avinashelite — Mon, 29 Jun 2015 12:54:51 GMT

try like this:

num(value,'###0.0000')

Re: Trouble with decimal settings ... again

rubenmarin — Mon, 29 Jun 2015 12:55:46 GMT

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.

Re: Trouble with decimal settings ... again

datanibbler — Mon, 29 Jun 2015 13:00:00 GMT

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?

Re: Trouble with decimal settings ... again

datanibbler — Mon, 29 Jun 2015 13:02:13 GMT

Hi Ruben,

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

I'll try that.

Re: Trouble with decimal settings ... again

rubenmarin — Mon, 29 Jun 2015 13:07:39 GMT

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.

Re: Trouble with decimal settings ... again

datanibbler — Mon, 29 Jun 2015 13:12:16 GMT

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

Re: Trouble with decimal settings ... again

sasiparupudi1 — Mon, 29 Jun 2015 13:17:14 GMT

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

Re: Trouble with decimal settings ... again

Anonymous — Mon, 29 Jun 2015 13:21:01 GMT

Hi DataNibbler,

Simply, remove the single quotes: =Round(24.09351, 0.0001)

Num(24.93051, '#.###,####0')

it works for all cases above

I hope that helps you

Re: Trouble with decimal settings ... again

datanibbler — Mon, 29 Jun 2015 13:21:23 GMT

Hi,

I have thought about the num# function already - but to be honest, I don't really understand how that works and so I am not sure how to use it.

You are not the first one to advise me to use that, giving the correct syntax - but, maybe because I have the software in German and the help_file is a strange mixture of english and German and does not really explain how the individual parameters of that function are made up, I don't understand how to use that.

Thanks a lot anyway!

Re: Trouble with decimal settings ... again

jonathandienst — Mon, 29 Jun 2015 13:22:50 GMT

If you use a Num format with a decimal indicator different to your system and.or environment, then you need to include the optional decimal parameter:

num(value,'###0.0000', '.')

num(value,'###0.0000', '.', ' ')

Re: Trouble with decimal settings ... again

datanibbler — Mon, 29 Jun 2015 14:38:58 GMT

Hi,

I still haven't come to terms with this. I'll give you all the information I have, maybe someone can help me.

- My settings (on the "Main" tab) are in German format (the comma is the decimal_separator).

- I have one numeric variable coming from Excel; In Excel, it is also in German formatting

=> Currently, I use a >> num(Replace('$(v_myvar)', ',', '.')) << to turn the formatting around and make it
numeric again.

- In the script I have two fields which I have to sum. One of them has variables (and is in German formatting), but
I round the sum, so it is a numeric value without decimals.

- I multiply that rounded sum with the variable and I get a numeric value again - I can see in the table_viewer that
it is numeric.

- There is a second value (also numeric) which has up to four decimals.

=> I want to round the first value to compare these two.

I think this is pretty complex. It would be helpful if anyone had a usable logical explanation of the num# function at hand (how to decide what the individual parameters must be) - the integrated help isn't really helpful here. Therefore I mess around with quotes to make it a string, REPLACEing characters and num() to make it numeric again. I guess that screws up my logic.

Best regards,

DataNibbler

Re: Trouble with decimal settings ... again

rubenmarin — Mon, 29 Jun 2015 14:50:51 GMT

Sorry but I don't understand the issue, can you upload a sample?.

I made a simple example:

-Created Excel with value 24,93051 in cell A1.

- Loaded and applied round, PFA.

In Spain we also have comma as decimal separator.

Re: Trouble with decimal settings ... again

datanibbler — Tue, 30 Jun 2015 06:56:28 GMT

Hi Ruben,

I don't fully understand myself what the actual issue is. It is quite complicated as and yesterday was already pretty hot - our office is always 2-3 degrees warmer than outside ...

I will try again today.

The thing is this:

- My app actually compares two reports, both of them in Excel

- In each of these reports, there are some time_measures and financial figures;

- There are a rather large nr. of records where, acc. to my analysis, the times are equal - everything is equal - but
the financials are still different.

=> That is what I actually want to analyze. I am pretty sure that it's a rounding thing, Now I have a numeric variable
that is also forthcoming from an Excel file and I think that's where the error lies. Let's see.

Is there maybe some document or blog_post around that clearly explains how I have to build up the individual parameters for the num#() function? As I said, the integrated help isn't very helpful in this instance and I think that would really help me.

The syntax that Sasidhar proposed seems to work, but I don't understand it. As I said, the help_file here mixes up German and English (and the formats) and it is kept very short (and incomplete) and rather adds to confusion ...

Best regards,

DataNibbler

Re: Trouble with decimal settings ... again

datanibbler — Tue, 30 Jun 2015 07:05:54 GMT

I guess that the issue, like some of you have already suggested, is the "comma". Even when a value is numeric all right, when it has a "comma", that is interpreted as the separator between parameters, even when the settings on the "Main" tab of the script are such that the "comma" is the decimal_separator.

Re: Trouble with decimal settings ... again

avinashelite — Tue, 30 Jun 2015 07:06:51 GMT

HI DataNibbler,

Please go through this thread to understand about the num# function

understanding the 'num#' function‌

Num#

num#(expression [ , format-code[ , decimal-sep [ , thousands-sep] ] ])

The num# function evaluates the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the default number format set by script variables or in the operating system is used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Number format	# ##0,#	#,##0.#

num#( A, '#' ) where A=35,648.375 returns:

	Setting 1	Setting 2
String	35,648.375	35648.375
Number	-	35648.375

num#( A, '#.#', '.' , ',') where A=35,648.375 returns:

	Setting 1	Setting 2
String	35,648.375	35,648.375
Number	35648.375	35648.375

num#( A, '#.#',',','.' ) where A=35648.375 returns:

	Setting 1	Setting 2
String	35648.375	35648.375
Number	35648375	35648375

num#( A, 'abc#,#' ) where A=abc123,4 returns:

	Setting 1	Setting 2
String	abc123,4	abc123,4
Number	123.4	1234

Re: Trouble with decimal settings ... again

datanibbler — Tue, 30 Jun 2015 07:18:26 GMT

Thanks Avinash!

The pasted text from the help_file doesn't help me on - I have read that a few times 😉 That's why I said that the help isn't really helpful here. But I will read through that post. I can't really believe that everyone else but me understands this function?

Anyway, if I understand it from that thread, I will make a short explanatory document out of that.

Best regards,

DataNibbler

Re: Trouble with decimal settings ... again

datanibbler — Tue, 30 Jun 2015 07:32:01 GMT

Hi Avinash,

so, essentially, the parameters I pass to the num#() function tell QlikView how I WANT the figure to be interpreted, not how it IS, right?

I didn't know about the exact function of the # vs. 0 either ... I see there is still a lot to learn.

(in my instance, the value is formatted as a number in Excel and reads >> 0,4017 << - but apparently, the "comma" is causing problems all along the way - the numbers might all be numeric all right and still, in the end I have two values looking quite the same, both with four decimals (and the same decimal_sign) - but an IF_query tells me they are not equal.

Also, do I necessarily have to wrap the first parameter (the original value I have) in quotes?