topic number format in QlikView

number format

Sun, 31 Oct 2010 18:18:48 GMT

I am loading world population data from wikipedia.

there's 1 table on the page, fine. it loads allright.

but: numers are in USA format - where comma separates thousands.

1,585,230 is tretated correctly as 1 million 585 thusand 230.

3,149 (which means 3 thousand 149) is treated as 3 and 14 hundreds!

This corrups totals, sums, averages and so on.

Please help!

/Dmitri

number format

Anonymous — Sun, 31 Oct 2010 18:36:14 GMT

Dmitri,
You can use num#() function to tell QV how to interpret the data, for example:
num#(field, '###', '.', ',') - read it as numeric where "." is decimal separator and "," is thousands separator.
If you wish, you can use num() around it to tell QV how to represent the data.
num(num#(...) [, format])

number format

Mon, 01 Nov 2010 06:20:37 GMT

yup.. i'm using that method too.

but, i'm trying to do this. NUM(Sales,'# ##0.##' , '.', ' ' ) &' USD' for a pie chart, and the pie wont work because of the concate string. any work around for this?

number format

Anonymous — Mon, 01 Nov 2010 10:30:49 GMT

Hi Nick,
First, what I'm suggesting for Dmitri is rather opposite: num# tells how to interpret the data, num tells how to represent the data.
Second, in your case, the 'USD' should be within format string, as in:
num*(sales, 'USD # ###.00')

number format

Mon, 01 Nov 2010 20:32:00 GMT

Thanks!

diggin further...

number format

Wed, 03 Nov 2010 14:45:37 GMT

I'm trying to format my claculated field from sql in US Accouting format. My claculations are of as in the original poster of this blog. I've used the suggested formula as

" num#(SUPPLIERCOST/PURCHASE_PRICING_UNIT_SIZE,'###','.',',') AS EXPRESSION_RESULT " but my results are still of and my test output of the field shows European formatting. How do I get my results and calculations as intended. Thanks.

number format

Wed, 03 Nov 2010 14:58:46 GMT

as I understand it, num# is to interpret data. Wrap it up in num() to change display(formatting).

data in SQL ----> num#(data), so that QV understands it as numbers -----> num(Num#(data)) So that QV displays numbers (that were made into QV numbers by #num)in the needed format

number format

Wed, 03 Nov 2010 15:15:53 GMT

out of curiosity, how you get data from wikipedia? could you give a little explanation?

hopefully not a nuisance...

Thanks!!!!!

number format

Wed, 03 Nov 2010 15:27:41 GMT

In the edit script dialog under FILE.

Choose load from webpages, paste URL. In my case i didn't need to do anything else as the particular W-pedia page only contained one table, which QV successfully loaded.

http://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population_density.

number format

Wed, 03 Nov 2010 16:15:18 GMT

I had not ever tried this option. Very interesting!

Thank you so much