String Manipulation (Multi-Column) - Conditional Formatting

[KaboboJakobo]

Hi everyone!

Complete noob here, I hope this crowd is nicer than stackoverflow’s lol,

In short I’m trying to learn Knime bit by bit ,

I started by using it to reformat a simple excel table, I was using it mainly for one task:

• Ensuring that all sentences of a specific column had a full stop at the end.

I used the following nodes and then tried this expression:
=REPLACE(LOWER(C3);1;1;UPPER(LEFT(C3;1)))

This worked out pretty well but I had two issues:

• Some of the cells had already a full stop at the end of their strings, so now they were doubled.

• Some of the cells were empty, and now they were populated by a dot.

I then attempted the following expression:

• join($$CURRENTCOLUMN$$,
• if(and(not(isMissing($$CURRENTCOLUMN$$)),
• not(endsWith($$CURRENTCOLUMN$$, “.”)))
• , “.”
• , “”))

And got this error message:

Im now blocked and a bit lost, any kind soul would like to help me out?

[takbb]

@KaboboJakobo

• join($$CURRENTCOLUMN$$,
• if(and(not(isMissing($$CURRENTCOLUMN$$)),
• not(endsWith($$CURRENTCOLUMN$$, “.”)))
• , “.”
• , “”))

Hi @KaboboJakobo , the String Manipulation (Multi Column) node doesn’t have the and, not, if etc statements that you are trying to use. It does however contain the ability to incorprate the functions listed within the node, along with some java constructs for conditions

Your code to add a full stop (period for those using US English ) where it isn’t already present, would be:

join($$CURRENTCOLUMN$$,
	$$CURRENTCOLUMN$$!=null && !($$CURRENTCOLUMN$$.endsWith("."))
	?"."
	:""
)

(edited as we can in fact use the java endsWith() method on a String column, which I hadn’t previously considered )

So:

Your Construct	String Manipulation equivalent
isMissing(x)	x==null
not(isMissing(x))	x!=null
not(x )	!(x )
and(x,y)	x && y
if(x,y,z)	x?y:z
endsWith(x,y)	x.endsWith(y)

Additional info on String Manipulation’s “undocumented” features

btw, welcome to the KNIME Community! I can’t possibly comment on Stack Overflow’s “crowd”, but I think you will find that the KNIME Community are (probably) the friendliest data community in the world.

[KaboboJakobo]

This worked like a charm! Thank you so so much for taking the time, your kind words and the ultra clear explanation

[takbb]

You’re welcome @KaboboJakobo , and great to have you with us!

[KaboboJakobo]

Thank you so much! I now have a follow-up question about using:

capitalize($$CURRENTCOLUMN$$,
". ")

The goal is to get all sentences after a period to start with an uppercase (currently not working for some reason).

Is there a rule about whether a follow-up question such as this one should come in a separate post or within this very same one?

[takbb]

Hi @KaboboJakobo , there is often some subtlety here in how to handle this.

Ideally once the original question has been answered, a follow up question, is best written as a new question unless it is a very simple / trivial clarification of what has already been said.

Only one response may be marked as a solution on a given question, so if your new question requires a new solution rather than being a simple tweak to the original solution, you’re more likely to get a response with it written as a new question. It may be that different expertise is needed, and also many forum contributors will see that the original is solved and so may not notice the new question.

By all means include a link back to the original though, if that helps with writing the question and lets you provide context without having to repeat yourself.

On the other hand, if your original question has not yet been answered and you feel that what you are adding is clarification or further detail of the same question, then including that in the original is often better than appearing to write duplicate questions.

In this case, I would suggest you put your new question as a new post as I think the original was about making the syntax work in String Manipulation, whereas I think from first glance, your new question may be more of an algorithm question on “how can I split out sentences and capitalise them?” and I suspect will require more than just the one String Manipulation node.

[KaboboJakobo]

Perfect, thanks a million, this community seems so welcoming, its really encouraging! I shall do a new post for this then