topic Re: How to format Numbers with some decimals? in QlikView

How to format Numbers with some decimals?

Thu, 07 May 2015 14:35:50 GMT

In one attribute I have a set of numbers that include the following:

10345

0.001

487.723

10.02

16345.1

My customers would like to see these numbers with commas and with no trailing zeros.

10345 -> 10,345

0.001 -> 0.001

487.723 -> 487.723

10.02 -> 10.02

16345.1 -> 16,345.1

When I apply a format of ###,###.###, I get trailing zeros on every number, which is not desired.

Is there a format option that will show both commas and only the significant trailing digits?

Do I have to use an IF stmt to detect the four cases (no decimals, 1 decimal, 2 decimal or 3 decimal) and format appropriately?

John

Re: How to format Numbers with some decimals?

Gysbert_Wassenaar — Fri, 08 May 2015 08:36:13 GMT

Try this format string: num(MyNumber, '###,###')

Re: How to format Numbers with some decimals?

buzzy996 — Fri, 08 May 2015 09:47:56 GMT

may be tis,

Re: How to format Numbers with some decimals?

Tue, 26 May 2015 17:11:35 GMT

Gysbert,

That format would not show the decimal portion of the value.

John

Re: How to format Numbers with some decimals?

Tue, 26 May 2015 17:12:54 GMT

Shiva,

The default option does not insert commas.

John

Re: How to format Numbers with some decimals?

swuehl — Tue, 26 May 2015 17:56:01 GMT

You probably need a conditional formatting, maybe like

LOAD Value,

num((Value),'#,##0' & pick(1+ len(subfield(text(Value),'.',2)), '','.0','.00','.000'),'.',',') as Value2

INLINE [

Value

10345

0.001

487.723

10.02

16345.1

];

The format codes description in the HELP talked about that something like this should/could work (except for pure integers):

'#,##0.0##','.',','

but it doesn't work at my side.

Re: How to format Numbers with some decimals?

rwunderlich — Tue, 26 May 2015 19:03:42 GMT

I remember discussing something like this a few years ago. Attached is the solution I used at the time.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: How to format Numbers with some decimals?

anlonghi2 — Tue, 26 May 2015 20:22:50 GMT

Hi John,

try setting the property "Number Format Setting" to Number

Regards

Andrea

Re: How to format Numbers with some decimals?

MarcoWedel — Tue, 26 May 2015 22:49:10 GMT

Hi,

maybe you could concatenate the formated integer part with the unformatted decimal part:

LOAD *,
    Subfield(Num(numbers,'#,###.###'),'.',1)&If(Index(numbers,'.'),'.'&Subfield(numbers,'.',2)) as numbersFormatted
Inline [
numbers
10345
0.001
487.723
10.02
16345.1
];

hope this helps

regards

Marco