Trouble with decimal settings ... again

[datanibbler]

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

[Anonymous]

Hi DataNibbler,

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

Or

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

it works for all cases above

I hope that helps you

[datanibbler]

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!

[jonathandienst]

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

or

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

[datanibbler]

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

[rubenmarin]

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.

[datanibbler]

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

[datanibbler]

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.

[avinashelite]

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

[datanibbler]

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

[datanibbler]

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?