Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

h_demarco
New Contributor III

Comma and dots as thousand

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=',';

...

A:

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

FROM E:\PSI*.csv;

...

B:

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?

Tags (3)
1 Solution

Accepted Solutions
Employee
Employee

Re: Comma and dots as thousand

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.

Capture.PNG.png

3 Replies
Employee
Employee

Re: Comma and dots as thousand

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.

Capture.PNG.png

h_demarco
New Contributor III

Re: Comma and dots as thousand

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.

veidlburkhard
Contributor III

Re: Comma and dots as thousand

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...

Hope this helps

Burkhard

Community Browser