Hello everyone,
I need to convert the number columns into accounting format. Below is the result in excel (before and after)
.
Any help would be greatly appreciated!
Thank you!
For the commas, have you tried this solution:
The output is string type (not sure if that matters for you).
For the negative numbers, I would suggest using string manipulation to join the () on either side of the stringed absolute value for all negative numbers.
For small numbers near 0, perhaps the Rule Engine is best here.
Hi @victor_palacios ! Thank you for the help! if it’s somewhere near 0, do I have to be using a rule engine to convert it to 0? Because in excel when we click on the rounded cell the original value displays in the formula bar, but KNIME wouldn’t be able to do that, right?
Yes, you would need two columns (one with 0 and one with the actual value) since hovering doesn’t change values in KNIME.
Hey, I’ve been working on a similar problem. Is there a way to have them in integers and not strings?
I’ve managed to get dots as thousand separators (EU), but when converted back to numbers, I lose them in the output file.
I’d appreciate any help with that.
There are several solutions here for that: Formatting numbers for webportal views - #4 by bruno29a
Thanks for the fast reply. I came across simple nodes such as String to Number, but when exported into Excel using Writer Node, the formatting simply goes off. This step right here only works in Knime and not when exported.
I also came across this post. This type of formatting is exactly what I need in my Excel exports (using Excel Writer Node).
If I understand correctly (I have 0 experience with Java) - the solution from @ipazin right here should solve the problem - meaning both fixing commas/dots and reformatting from String to number (double)?
If so, does anyone have any working workflows with this code in Java Snippet node? It might be, I’m doing something wrong.
I’ll gladly read, learn whatever is needed to solve this, but it seems I’m “running in circles” with this problem.
Any new reading materials or any other type of help will be much appreciated!
@helpmeplease I think you can have the accounting format in knime only as string since knime will use its own internal storage of numbers. If you want to have this format in Excel. Maybe the way to go forward would be to just export the regular numbers and then change the format in Excel:
Another approach to this could be to use Python and Openpyxl like this - but maybe you start with the Continental nodes. I can see if I can come up with an example.
Hey, actually the xls cell formatter node seems to do the job. It displays the negative values in brackets, if we select finance from the drop down.
Thank you very much for the fast response!
@Saishiyam thanks for the response! Would you be willing to share the workflow with me? XLS Cell formatter probably needs to be connected to other Continential Nodes in order to work properly? I can’t just convert Number to String, use this Node and export it via Writer.
Kind regards!
Hi @helpmeplease, I referred to this workflow for the same
Thank you very much!
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
