have to add a $ symbol to my round double column

Number with $ - Community Extensions - KNIME Community Forum

I am looking at above post where it says a solution, but didn’t know how ti use it.

can anyone share a sample wokflow on how we can add a $ to a numeric column, i cannot convert it to string as it has to maintain the , and the .

My sample data

Expected output
$11,376.25
$10.00
$10.28
$1,184.56
$11,741.78
$10.00
$70.54
$12.44
$3,119.22

What does your input data look like and what is its data type? If you plan to write the data to Excel you can use the Continental formatter nodes. I may be wrong, but I don’t think there’s a way inside KNIME to use a numeric type with a $ sign - strings only.

3 Likes

My Input datatype is round double, as I need to have 2 decimal places.
I need to write this date to a xlsx.
Can you help with example on how to use continental nodes.
MY Input looks like this

11376.25
10.00
10.28
1184.56
11741.78
10.00
70.54
12.44
3119.22

1 Like

Hello @chaithuj ,

Excel templates can be used to address formatting issues without changing the data type of the original data, but a pre defined Excel template is required.

I provide two methods for comparison, one is to add a currency symbol before the number, and the other is to use an Excel template.

Excel Template.knwf (36.2 KB)
Tip: This workflow was written using version 5.2.3 of KNIME, and I am not sure if there will be any issues opening it in other versions.

1 Like

Hi @chaithuj , if you do want to try the Continental Nodes, there can be a bit of a learning curve, when you want to just “get it done”. For this reason, I wrote a couple of components to assist. I have two different approaches below depending on your use case.

In both cases below you will need to have the Continental Nodes extension installed. In the first case, the Continental Nodes are encapsulated behind the scenes and remain invisible to you:

Here you would use my XLSX Simple Excel Format Apply component

This makes it really simple to set a standard output for ALL doubles:
image

The downside is that if you have data where only a subset of the Doubles are currency, it would format them all as currency:

image

The second option is to actually use the Continental nodes for more fine-grained formatting. But the learning curve on this extension, in my view, is in working out how to define the “control table” and “tags”, which is what it uses to define ranges of cells to be formatted. I hope that by using the technique below, this learning curve is significantly reduced.

…Because this is where my other component comes in… and I use this whenever I am working with the Continental Nodes :wink: :

This component generates a control table with “tags” for a wide variety of common use cases.
Each column is allocated a tag “c:columnname”
The header row is allocated a tag “header”
Data rows are allocated the tag “data”
and more besides.

For this data table:
image

It automatically generates this control table without you having to configure anything:

and , as shown in the highlighting, every data cell in the “Value” column is given the tag “d:Value”

All you have to do then, is use the XLS Cell Formatter to specify the format to be used in any cell with the “d:Value” tag:

And in total, writing the currency format for a specific column is as simple as this:

image
Write currency data to Excel with Continental Nodes.knwf (848.1 KB)

Further reference:

edit: at the risk of being accused of going a bit “over the top” :wink:

If you want some of the ease of the first option, but with the fine-tuning of the second option above, there is a further component I can introduce: XLS Simple Format Controller

Drop that in after the Auto Tag Generator like this…


In the above I have demonstrated the addition of a “totals” row, which shows the total of the Value column.

With the above workflow, you can see how (relatively) simple it is to now produce an output like this:


Write currency data to Excel with Continental Nodes - enhanced.knwf (485.4 KB)

3 Likes

As usual @takbb has provided elegant solutions. I’m always in awe of his contributions. Here’s a simple workflow which uses a very basic structure to convert your base numbers to dollars.

1 Like

I always like to throw in the Write to Excel Template node in threads like this as well. It is a lesser known option, but is infinitely easier / quicker to work with and also opens up the doors to some amazing advanced Excel integrations. (Conditional formatting, user interface designs, dashboard style presentations, easy user friendly control settings for complex KNIME components, etc.)

If it is just basic formatting, then I find the quickest solution is to use the Excel Writer to first output your unformatted Excel file. Open the excel file and setup your formatting, then clear the data. Then point the Excel Write to Template node toward that as your template file. Super quick and easy. I also like to keep my template files in the workflow data folder so they remain bundled with the workflow if you export it for sharing.

1 Like

Thanks @rfeigel and I agree @iCFO that Write to Excel Template opens up many more of Excel’s features.

My outputs to Excel are generally much simpler tabular reports, and I was (kind of) determined to make it easier for me to use the Continental nodes to write quickly formatted data without any manual editing of Excel.

Without a doubt, my components to perform Control Table generation, and to make the formatting “simple”, are (behind the scenes) some of the scariest to look at, that I have written!

1 Like

My clients are spoiled. :rofl: Before the Write to Excel Template node and your formatting components came along (Thanks for those by the way!), I would quickly knock out a workflow and then put a full day + into trying to clean up the formatting piece by piece and sheet by sheet with the Continental nodes. Even after that, I was regularly spending hours adjusting them based on the clients ongoing formatting requests.

I could do an entire web series just on the cool tricks and solutions that are opened up with the Template approach. I would go as far as to say the the Template node by @AnotherFraudUser dramatically improves the overall KNIME AP software functionality as a whole by filling the gaps of sharable and portable user interactive settings / reporting with live calculations. You can do amazing things in Component interactive views if you spend an extreme number of hours dialing it in and creating workarounds, but they are complex / require constant manual refreshes / are limited in formatting flexibility & UI customization. Complex interactive components also require a good bit more update maintenance and have limited portability options as they need to be deployed via sharing the entire workflow for local use, Teams Hub or Business Hub. I am not putting interactive view based components down… I certainly use and love them! I just think too many users see KNIME as an “Excel replacement”, when it is also an Excel multiplier (and vice versa). The more heavily I leverage how much KNIME and Excel compliment each other, the more I am convinced that they combine into the ultimate multi-tool!

Many of my more complex in-house components now have the bulk of their UI and Settings in an excel file which can be launched for changes via a manual setting the Component’s Interactive view.

3 Likes

Thank you @tomljh
I will try this

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.