Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

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.

Highlighted
Not applicable

number format

I had not ever tried this option. Very interesting!

Thank you so much

Community Browser