Hi Friends,
I have a question how to convert string column into number/decimal column type
Sample image below
0.00
0.00
0.00
150.00-
0.00
0.00
0.00
I tried this code using Column expression
abs(removeChars(removeChars(column(“Product Amount”)),","))
No error when I click evaluate, but when I execute the node
Error Message: ERROR Column Expressions 0:85 Execute failed: (“NullPointerException”): null
There is a node called -string to number- which can do this if the numbers are correctly written:
Otherwise, you would need anyway to correct their syntax. For instance, here the “150.00-” should be change to “150.00” before applying this node. Unfortunately you would need first to gather all the possible “wrong syntax” cases, treat them beforehand and then apply the -string to number- node.
Hope it helps. Otherwise happy to further help.
Best
Ael
Sorry I forgot to add the information about the value that contains “-” is a minus -150.00
this shows in my txt file.
No problem at all. Then it should be ok with just the -string to number- node.
I tried using string to number node
It works, Only in value that has no “-”, otherwise the result is “?” in output.
I your post, the minus appears after the “150.00” as “150.00-”. Is this really the case in your KNIME table ? If so then it is normal that it fails. Could you please do a snapshot of your table to show us how the data looks in a KNIME table ? It would help us to understand what is going on 
Thanks
Ael
Here the data looks
Before, I run to string to number
This Data looks after i execute in string to number
After, I execute to string to number
Kindly look the yellow shades especially in Row 85 and 97.
Thanks for your patience!
If that "-" is a minus, you can move it to the left using this bit in a String Manipulation node right before the String to Number node:
regexReplace($yourColumn$, "(.*)-", "-$1")
If you want the "-" removed, use this instead:
removeChars($yourColumn$, "-")
Hi @seanmanzanilla & @Thyme
Following @Thyme excellent suggestion, please find his solution here below with as complement, a purely KNIME solution
20220209 Thyme How to convert string column into number with negative values.knwf (44.3 KB)
@seanmanzanilla, please validate Thyme’s post as solution (not mine) since it is much better, if you accept the solution.
Thanks & best regards,
Ael
Hi @Thyme it works for me, the formula of regexReplace. Thanks a lot!
Last question what is the meaning of this “(.*)-”, “-$1”) can you please explain it to me? for better understanding thank you!
Hi @aworker yes, I like your idea. thank you for showing different process.
Thanks a lot guys!
It’s RegEx substitution. I only learned that this exists last week.
We want to match any string followed by a "-". Everything except the "-" should be kept, so we put the “any string” into the round brackets. In the replacement, we use our variable ($1), with an additional "-" on the left side. Strings that don’t match are not changed.
. matches any character
* zero, one, or multiple occurences of the preceeding character
.* matches any string
() to use something as variable. variables are denoted with a $ followed by the variable number
Hi @seanmanzanilla , on top of what you have learned from @aworker 's and @Thyme 's posts, I hope you understood what the issue was.
Basically the String to Number converts the type from String to Number, but the string being converted has to be a valid number. “150.00-” and “1,000.00-” were not valid numbers. So Knime did not know what to convert them to, hence why you get a missing value (red ?) instead. On the other"-150.00" and “-1,000.00” are valid numbers.
One confusion was that you mentioned this:
This meant that the real value was -150.00, and “150.00-” was just a display thing (and that is why @aworker had said there should not be any problem), or so we thought. I’m not sure what was the deal with these numbers as clearly they did not seem to be -150.00 or -1,000.00 since Knime did not convert them.
That is also why your abs() function did not work.
This is because Evaluate button only evaluates the first row. Had you had “150.00-” in the first row, Evaluate would have failed.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.