topic Re: Number format: what is the difference between the num and num# functions? in QlikView

Number format: what is the difference between the num and num# functions?

Tue, 24 Jul 2012 12:57:52 GMT

I cannot understand the difference between the num and num# functions.

The documentation says:

The num function formats 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 number format set in the operating system is used.

and

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.

Could somebody explain the difference in a clearer way?

Re: Number format: what is the difference between the num and num# functions?

nagaiank — Tue, 24 Jul 2012 13:14:45 GMT

Number: 1234.56

Number representation string: 1,234.56

format-code: #,###,##

Number = Num#(Number representation string, format-code);

Number representation string = Num(Number, format-code);

Re: Number format: what is the difference between the num and num# functions?

Tue, 24 Jul 2012 13:31:35 GMT

Thanks for your answer, it is clearer now. However I am still struggling on my original problem: I want to get a number formatted with a dot ('.') as decimal separator and no thousand separator, while keeping all non-zero digits in the decimal part, without any dependencies on the current system formatting.

I tried (X being the number I want to format):

num(X, '#.#', '.', '')

But it does not work as I want: it rounds X to display only its first decimal digit. I can add some '#' after the decimal in my format but in this case a value of "0.5" will be display as "0.5000000".

My goal is to be able to parse a long string containing both text and number values without having to worry about the locale system settings (for an extension object). Do you know how I can achieve that?

Re: Number format: what is the difference between the num and num# functions?

nagaiank — Tue, 24 Jul 2012 17:38:59 GMT

Did you try Num(X, '#####.#') ?

If you want to limit the number of decimals, you may use round() function.

Re: Number format: what is the difference between the num and num# functions?

Wed, 25 Jul 2012 08:14:00 GMT

Yes I tried but this is not what I am looking for. I want the number to have as many digits as necessary to prevent precision loss but do not want trailing zeroes if they are not needed.

If I use num(X, '####.#') then I get only one digit after the decimal separator, and the number is rounded if there are more digits needed.

How can I have 3 digits for "4.839" and only one for "5.6"? It seems that I am stuck with either "4.8" and "5.6" OR "4.839" and "5.600"....