Need help to remove commas after combining multiple columns

[barnalinath]

Dear all,

Hope you are doing great!

I am new to Knime and while trying to build some workflows, I am stuck at a point where I will require your valuable guidance and help.

So at this stage of my workflow, I have combined 8 different columns and it gives me an outcome where it has commas at the beginning, at the end or in the middle of the outcome.

I used column combiner/column aggregator (with unticking the “missing” box), string manipulation/string replacer node but still the commas remain same or if I remove delimiters or use replacer, it removes all the commas thereby combining all 8columns without any Seperator. My input and output look something like this:

Example 1:

Before joining
column A-H= null (First row)

Then I get this result:
, , , , , , , => comma as a separator for each column even though there is no value

Example 2:

Before joining (2nd row)
column A = A1, A2
column B = null
column C = C1, C2
and so on…

Then I get this result:
A1, A2, , C1, C2 => commas in the middle of the string sequence

Example 3:

Before joining (3rd row)
column A = null
column B = B1, B2
column C = C1, C2

Then I get this result:
, B1, B2 C1, C2 => commas at the beginning of the string sequence

Example 4:

Before joining (4th row)
column A = A1, A2
column B = B1, B2
column C = null

Then I get this result:
A1, A2, B1, B2, , => commas at the end of the string sequence

and so on…

How can I remove these commas at the end or at the beginning or in the middle of the string sequence?

Thank you!

Kind regards
Nath

[iCFO]

Do your “null” values show as “?” in KNIME? My guess is that they are blank values “” instead. If that is the case then you can just replace the “” values with missing values and bypass the issue from the start.

[barnalinath]

Hello,
Thank you for addressing my query promptly.

Yes my null values were indicating as “?” before and my outcome was something like this for first example/first row: ?,?,?,?,?,?,?,?
so I changed it to “” before combining and then I am getting the outcome as: , , , , , , ,

[iCFO]

column combine.knwf (24.1 KB)

Here are a few options to combine them into a list / collection and then back to a string (if necessary)

[barnalinath]

Thank you!
I do not have “collection to string” node in my repository. I am using V4.5.1 and not sure how to download it.

[iCFO]

You can drag the node image from the below link onto your main window in KNIME, or you can go into KNIME AP click on file then install extensions and select Vernalis Nodes.

Collection to String

[iCFO]

I also added another way to do it with base nodes.

column combine.knwf (35.7 KB)

[barnalinath]

I am so sorry, looks likes nothing works for my app
Dragging gives me below error:

and installation gives me another error:

[iCFO]

@barnalinath - Take a look at my post just above this one. I added a 3rd way to do it to the workflow with just base nodes.

[iCFO]

Also, to address your ability to install additional nodes.

I believe that you will need to add options to your “Available Software Sites” which can be found under File - Preferences - General - Install / Update.

I am using 4.6.1, but here are my sites.

KNIME Analytics Platform 4.6 Update Site https://update.knime.com/analytics-platform/4.6

KNIME Community Extensions (Experimental) https://update.knime.com/community-contributions/4.6

KNIME Community Extensions (Trusted) https://update.knime.com/community-contributions/trusted/4.6

KNIME Partner Update Site 4.6 https://update.knime.com/partner/4.6

NodePit https://download.nodepit.com/nodepit/4.6

[barnalinath]

Thank you for being so patient and helpful.
I have already used all these possible base node combinations, but the result remains same.
I just tried this one again but no luck. The commas are always there.

[iCFO]

Please upload a workflow with some sample data in it and I will take a look.

You can do this by going to file - export KNIME workflow - (then make sure that you “uncheck” Reset Workflows before export.

[barnalinath]

Sure, my workflow is huge and has lot of steps including multiple rule engines, I am sharing a simpler version, kindly have a look.
KNIME_project.knwf (31.7 KB)
thank you!

[iCFO]

column combine.knwf (61.3 KB)

The formula for “,?” doesn’t work for the 1st column since it isn’t preceded by a comma. It just needed another replace for “?,” to remove the 1st column missing values at the beginning of the string.

[barnalinath]

unfortunately, it didn’t work for my workflow. it doesn’t have “?” mark, it only has commas and no matter whichever step I take, it remains
May be because the combination columns come from multiple rule engine
I just need something which can remove extra commas but retain the ones which seperates actual strings.

[iCFO]

column combine.knwf (53.5 KB)

Here is another approach that I remembered using a few versions back.

[iCFO]

column combine.knwf (35.2 KB)

Here is more of a brute force “base nodes only” workaround for you. I combined the columns using the String Manipulation Node to recreate your problem on my end, then I just used a series of the String Manipulation Nodes to handle all of the comma problems.

[barnalinath]

Thank you, it works partially. Appreciate all your assistance. Good day!