number format

[#1]

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

[Anonymous · Accepted solution]

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

[#3]

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?

[Anonymous]

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

[#5]

Thanks!

diggin further...

[#6]

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.

[#7]

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

[#8]

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

hopefully not a nuisance...

Thanks!!!!!

[#9]

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.

[#10]

I had not ever tried this option. Very interesting!

Thank you so much