Number format, num# function and decimal separator problem.

[Igna]

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-
];

[StarinieriG]

Hi

try to use these variables

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

[Igna]

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

[StarinieriG]

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

[Igna · Accepted solution]

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