Convert a number with commas as thousands separators

[mauuuuu5]

Hi everyone, I am trying to convert a number with commas as thousands separators, so I guess the number shall be converted to a string and then try to apply the format.

I have found some JavaScript examples online, but when applying the solution it just converts the number to a string without the separators.

Any help will be appreciated

Best Regards

Mau

[takbb]

Hi @mauuuuu5, does the following link give you the help you need, or at least give you some pointers? It uses java rather than javascript…

How to format

Hi. Am I correct to assume you would like to format numbers such that the ‘thousands separator’ should be an apostrophe instead of a comma? For example how they format numbers in Switzerland? Here’s a quick and dirty solution using Java. (I created a metanode you can copy into your own workflow and configure). Import the attached .knwf file into your KNIME desktop explorer. [image] The metanode can be configured with the column of (double) numbers you want to format, and the formatting patter…

[mauuuuu5]

Hi @takbb thank you for the link, I have not seen that post. I downloaded the workflow and tried and it partially works, as you have to manually change the column name inside the Java Snippet node and not in the component despite that it has a dropdown list to “select” the column.

In summary the solution is nice but in my case I need to apply it in several columns.

Thank you

Mau

[mauuuuu5]

Hi everyone I found a workflow that adds the commas to a string

Display commas – wkhan

Based on the previous solution I did a slight modification in case your input column is a double type column

join("$",column("VAL_A").toLocaleString('fullwide', {useGrouping:false}).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,"));

Column Expression Question.knwf (11.1 KB)

Thank you

[bruno29a]

Hi @mauuuuu5 By the way, the String To Number node allows you to specify Decimal separator and Thousand separator:

As the name says, it takes a String as input, so if you have an integer/double/long column, you can convert it to String, and then back to Number like this:

Input values (Table Creator):

Results:

[ipazin]

Hello @mauuuuu5,

workflow you found is from this discussion:

Formatting numbers for webportal views

Hi fellows, I had quite some trouble to display numbers in the webportal in readable fashion with a thousand-seperator " , " within the “Table Editor” node. Generically, it seems that number formats don’t come with seperators (except decimal seperators, which you can easily check in the node configuration itself. Would be cool to have this feature for thousand seperators as well.) (see example screenshot 1). The number to string does not work, as a decimal seperator is set straight after the f…

But have tried Component from topic linked by @takbb and seems it works as expected although my column column is configured as input. Probably worth upgrading Component regarding this and adding multiple column support…

Br,
Ivan

[takbb]

Hi @bruno29a , I liked your answer but it had me intrigued as I couldn’t see how a numeric could remain as a Double and yet contain commas… following String to Number. So I had to investigate further.

So when I tried it out, firstly telling it to use “,” as thousands separator, sure enough I got this…

so… still puzzled, I tried using “x” as a thousands separator…

and I still got this

And then I realised that the “thousands separator” on that screen indicates the thousands separator on the incoming string (if there are any) is, rather than on the outgoing number, and that the commas appearing on the output for that node are just (coincidentally) from the renderer and haven’t been included in the number after all. Sadly, if you try to do anything with the number, such as String manipulation

you’ll find that under the hood its still the same old double value without the formatting as it isn’t really there

sorry to be bearer of bad news…

[mauuuuu5]

Hi Ivan thank you, I will check it out

[bruno29a]

Hey @takbb , as usual, thanks for the input.

Indeed, the options look to be characters to look for on the incoming string, and it kinda what the option says (which I missed):

It looks like the String to Number would add the thousand separator regardless.

In relation to the separators not being included in the number, it’s definitely something that I would want, as the separators should be for display only - it all depends on what want to achieve in the end, and this is a personal preference.

My reason being, I don’t want to lose the real value of these records, in case I need to do operations on them, and you can’t process them if they physically have these separators in them. With the separators physically in them, they’re basically strings - think of storing these values in a db table, you would want to keep them as numeric in the db, and render the separator only for display.

Again, it depends on what you want to achieve

[dnaki]

Hi. I have developed a component that converts selected numeric columns to String by specifying a locale. The locale controls the characters used for things like thousands separator, decimal separator, etc. Additionally, a decimal format pattern can be used for finer control of the output format (number of decimal places, whether to use scientific notation, currency formats, etc.).

I have supplied a number of predefined formats. Please give it a try and provide feedback if you are so inclined. Any suggestions for improvement appreciated.
Thanks,
Don

Here’s the dialog of the component:

Number To String (Locale).knwf (39.0 KB)

[mauuuuu5]

Hi @dnaki I will try the component thank you very much

[ipazin]

Nice one @dnaki
Ivan