topic Re: Number format, num# function and decimal separator problem. in QlikView

Number format, num# function and decimal separator problem.

Igna — Sat, 16 Nov 2024 02:01:50 GMT

Dear all,

I'm finally posting this thread because I could not find a clean solution for this issue with the number formatting .

Below there is an example and as you can see, the decimal part is treated as integer.

Of course, it is achievable with workarounds like replace() or by changing the settings. However I would like to know what I'm overseeing.

Thanks for your help!

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

Load*,
num#(Number,'###0.##;###0.00-','.') as function
;
LOAD * INLINE [
Row, Number
1, 128.90
2, 326.61
3, 703.89
4, 1144.63
5, 4608.53
6, 4828.99
7, 5854.20
8, 5881.26
9, 111043.16-
10, 2413.76-
11, 1089.24-
12, 572.60-
13, 552.79-
14, 160.20-
15, 3.52-
];

Re: Number format, num# function and decimal separator problem.

StarinieriG — Tue, 05 Nov 2019 16:54:14 GMT

try to use these variables

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

Re: Number format, num# function and decimal separator problem.

Igna — Tue, 05 Nov 2019 16:56:41 GMT

Thanks for your fast answer. Yes, as mentioned above, it works.
Nevertheless I would like to understand why that other approach does not work.

Re: Number format, num# function and decimal separator problem.

StarinieriG — Tue, 05 Nov 2019 16:58:51 GMT

Because when you use num function, Qlik use informations that you have inside those variables. Even if you use Num# inside the script.

You have to use Num# exactly before Num, anyway I think it will not work

Re: Number format, num# function and decimal separator problem.

Igna — Tue, 05 Nov 2019 17:12:13 GMT

In order to provide a counterexample I found the/a "solution", however I do not like it either; it looks like Qlikview is waiting for a separator, even if you do not have any, it is a little bit misleading then:

num#(Number,'###0.##;###0.##-','.',',') as function

(it also works, for example, with ' ')