topic Comma and dots as thousand in QlikView

Comma and dots as thousand

h_demarco — Tue, 21 Oct 2014 18:01:25 GMT

People, I need some help.

I have CSV file where values are integers (values represents number of units) separated by comma as thousands.

My system default is comma as decimals and dot as thousands. So here comes the confusion.

CSV files are loaded as [ValorTemp] field and after becomes [Valor] as follows:

SET ThousandSep='.';

SET DecimalSep=',';

...

LOAD Distinct * //here there is a field [ValorTemp]

FROM E:\PSI*.csv;

...

NoConcatenate

Load Distinct *,

Num#(ValorTemp, '###', '.' , ',' ) As Valor, //[ValorTemp] becomes [Valor]

Resident A

Where ValorTemp <> 0;

Drop Field ValorTemp;

Drop Table A;

In CSV I have an entry as 1,320 (one thousand three hundred and twenty units). In Qlikview table 'B' I also have 1,320, but I am not sure if it is being considered as "one thousand three hundred and twenty units" or "one unit and third two hundreds".

My need is to have Qlikview [Valor] field as 1.320 meaning "one thousand three hundred and twenty units". As my data has only integers entries, a comma should never appear according to my system default.

Can anyone help me to find the mistake?

Re: Comma and dots as thousand

JonnyPoole — Tue, 21 Oct 2014 18:16:52 GMT

Regardless of what you loaded in here is an example of formatting a chart and text box with the same numbers the way would want.

You may want to wrap your num#() function in the load with a num() function that formats it the way you want. Think of num#() being used to tell qlik how to intepret the source values and num() telling qlikview how to display the result by default in the UI. num#() is input , num() is output and you can nest/wrap them.

Re: Comma and dots as thousand

h_demarco — Tue, 21 Oct 2014 18:58:52 GMT

Jonathan, thank you very much.

Despite having a way to solve it by using properties tabs, your explanation about input and output was the solution of my problem. I had no idea about Num# and Num.

Thanks.

Re: Comma and dots as thousand

veidlburkhard — Tue, 21 Oct 2014 19:04:52 GMT

Hi Henrique,

please follow this link to learn all about Num# and Num, Date# and Date:

http://community.qlik.com/servlet/JiveServlet/previewBody/3102-102-4-4191/DateFields-TechBrief-US_v2.pdf

Hope this helps

Burkhard