Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

number format

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

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

number format

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

9 Replies
mov
Esteemed Contributor III

number format

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

Not applicable

number format

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?

mov
Esteemed Contributor III

number format

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

Not applicable

number format

Thanks!

diggin further...

Not applicable

number format

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.

Highlighted
Not applicable

number format

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

Not applicable

number format

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

hopefully not a nuisance...

Thanks!!!!!

Not applicable

number format

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.

Not applicable

number format

I had not ever tried this option. Very interesting!

Thank you so much

Community Browser