How to format Numbers with some decimals?

[#1]

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

[Gysbert_Wassenaar]

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

[buzzy996]

may be tis,

[#4]

Gysbert,

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

John

[#5]

Shiva,

The default option does not insert commas.

John

[swuehl]

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.

[rwunderlich]

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

[anlonghi2]

Hi John,

try setting the property "Number Format Setting" to Number

Regards

Andrea

[MarcoWedel]

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