numbers without thousand separator ?

[Lbenky]

dear helping community,

how can i get rid of the thousand separator in numbers (format D, decimal), from CSV or Excel source ?

my case:

• I properly get numbers (format D) in a Price column from Excel,
• then I intend to JOIN a product classification : i that stage my Prices turn to String
• then I intend to Convert (String to Numbers) my prices : at that stage the thousand separator bugs
  (see screen capture)

is there a setting that I can define somewhere to get rid of the separator (whether it is a " " space, a comma or a point) ?

[lelloba]

Hi @Lbenky,

could you please upload a small example of your data and workflow?

Thank you,
Raffaello

[FrankColumbo]

Hello Lbenky

Here is an idea to deal with this:

use a String to Number node and set the config to this:

You will still have 1000 separators, but these are for visual purposes. You can still do calculations:

Hope this helps

Frank

[Lbenky]

Many thanks for your question
preparing for requested extract i realised the issue was from the source: the price column had

[Lbenky]

thank you Frank, the issue was in the Dataset

all the best

[rogerius1st]

Hi, @FrankColumbo,
I have a (somewhat) similar problem.
My task is to begin working on the CSV file:
IBGE_RS_Popul_Censo_2022_por_Munic.csv (15.2 KB)
I just need two columns of this file: a) Nome_Munic (the name of the municipality), and b) Populacao (the population of this municipality). Both of these columns are on the “String” type.
I first used the node “String to number”, with the option “Integer” type for the output, and applied the “thousands separator” as “.” and also tried with or without the “decimal separator” as “,” but after several trials, I got no thousands separator at all on the output of this node. The only exception I got was when I applied the option “Double”, but it didn’t make much sense to me, because the file describes the number of inhabitants per city, and therefore there are only integers for such a reality… And even so, I only got a comma (“,”) for “thousands separator”, but not a “.” (which is the standard for Brazil).
Would you mind help me with this (probably elementary) question?
Thanks a lot.
B.r.
Rogério.

[FrankColumbo]

Hello @rogerius1st

I just seen your message today.
I have upload what I was doing to the KNIME hub, here is the link numbers without seperator question – KNIME Community Hub
here is what the working flow looks like

In row121 (row122 in excel) there is a letter - “l” - before the number. This is why The String To Number Node wasn’t working. This is the only Letter I found in the column - see the output of String Manipulation Node Find letter.

Depending on your needs you might want to strip out all letter in the column no matter what. if you do look in the String Manipulation Node replace letters with “”.

There are some rows with have a missing value e.g. row39. Depending on your needs you can replace that missing value with something else using the Missing Value node. I have just set up the node to replace all missing values with -1, but, this node can do much more.

I tried multiple times in different ways to get the String to number node to add “.” as a thousand separator and could not. I also looked and other nodes.

Hope this helps

Frank

One more thing, If you do find out how to make a thousand separator a “.” please could you let me know.

[denisfi]

Hi guys,

Just a tip here… you are talking about location settings, so knime have this configuration too to be make as US system or you can set for your location. You can manipulate it too as you wish, but I think more comfortable if you set as your needs.

At File > Preferences > Prefrerence topic, you will find the render option for basic settings and advanced ones.

Althought some nodes as Date&Time to string can convert the date format for your local language, it’ll works as normally (us) mark. The result can be exported as you need.

String to Date&time node

Date&time to string node

String to number node

Maybe it help you…

BR,

Denis

[FrankColumbo]

@denisfi Cool!

If I was to change the preferences would that change be universal? e.g. would it change across every Workspace, Or could I have a work space for Brazil and one for Germany and one for anything else I desire etc?

I can’t experiment on my work KNIME instance!

Frank

[denisfi]

For me, that’s preferences for the knime application at all… BUT the Knime team experts can help us about it…

I work only with Brazilian data, so never test it outside my fields… lol

BUT, if it help all community, let´s try!

@ScottF @AlexanderFillbrunn , can you bring some insides about it?

Thanks,

Denis

[AlexanderFillbrunn]

Hi,
Generally, preferences are per-workspace, except the proxy settings. So I would assume different renderer settings could be used by switching workspaces. However, be aware that this is just the renderer. I do not think that it also applies to the default number parsing configuration in nodes like String to Number.
Kind regards,
Alexander

[Lbenky]

@denisfi

Just a tip here… you are talking about location settings, so knime have this configuration too to be make as US system or you can set for your location. You can manipulate it too as you wish, but I think more comfortable if you set as your needs.

At File > Preferences > Prefrerence topic, you will find the render option for basic settings and advanced ones.

Very helpful thank you I had not set the preferences indeed.