Create column with values of a second or third column

Hello,

I’m locking for a solution for the following problem.
I want to create a new column with values of the second “or” third column. It depense where I have values.
Example:

Col1 Col2 NewCol
AAA AAA AAA
BBB BBB
CCC CCC

I used the node “String Manipulation” in two cases two times. The first combination it seems that it work. In the second case it doesn’t. And I can’t understand why. Here are my Expression in the node:

replaceChars($COL1$,null,$COL2) --> First node
Append column: NewCol --> First node
In this case the column NewCol is completely filled.

replaceChars($COL2$,null,$COL1) --> Second node
Replace Column: NewCol --> Second node
In this case some columns field in NewCol are empty. I tryed to use “Missing Value” and set all empty fields to null. But it doesn’t helped.

Maybe I used in general the wrong node.

Thanks
Jürgen

Hi Jürgen,

hopefully i understand your question correctly.
You can do it with e.g. java snippet node. See screenshots.

Demo 2

BR
Hermann

Hello Hermann,

many thanks for your fast reply. The Java Snippet does the same in one step what my String Manipulation did in two step.
But unfortunatelly the results are the same. Not all values will be copied to my new one Col3.
I changed the variables Col1 and Col2 in your script. The result is that the if clause works proper but the else case not. In your example are the fields in Col3 are empty when Col1 is also empty. I tried to use the Missing Value Node and fill out all empty field with a hyphen but it doesn’t work neither. I have no idea to solve my problem.
BR
Jürgen

Hi Jürgen,
can you show me a screenshot of your base dataset to get an impression what causes the trouble.
It would also help if you could show an example of the expected result.

In general missing values can be handled in java with null-Statement and has not be replaced by any character or value.

BR
Hermann

There is a node for this :slight_smile:

1 Like

Hi together,
Thanks for the answers.

I made a screen shot of my table. Sorry for pixelate but I’m working with real data and I wanted to show you the problem.
@Iris: I tried the Column Merger, but I get the same results as at the other possibilities.
Screenshot1:
Primary Column: Column1
Secondary Column: Column2
Append new Column: Column3
KNIME Column Merge1

In Screenshot 2 I reversed Primary and Secondary.
Primary Column: Column2
Secondary Column: Column1
Append new Column: Column3

It doesn’t matter which order I choose, there are always empty fields. In all case, Java Snippet or Column Merger.

BR
Jürgen

Hi Jürgen,
one simple question to rebuild the your problem. I assume you had joined 2 tables together using “full outer join” option. Right?

BR Hermann

Hi Hermann,

That’s right.

Hi Jürgen
i just checked it but this can not be the issue it works well.
Please, can you check if those rows are real empty using java snippet (simple) with statement “return $column1$.length();” and option Insert missing as null selected?
My gut feeling told me that there are hidden characters.

Hi Hermann,

for both columns the return code for the fields which weren’t copied is 0. The other fields had return code 10.

BR
Jürgen

In the string manipulation mode, there is a check box to “insert missing values as null”. Using that you can join all columns and the any missing values will be ignored.

You can also use the column expression node. It allows complex logic via javascript. So you can use if/then/else statements to combine data.

1 Like

Hi Jürgen,
your table shows for missing numeric data the ‘?’ and this means they are missing. But not for your Text data! Therefore missing value does not work.
If you change the if-statement of my example to if(c_Col1.length() > 0) {… etc. it should work.

BR
Hermann

1 Like

Hi there @juergen01,

problem is (as @morpheus said) you don’t have missing values in your columns and thus provided solutions don’t work. This one using Rule Engine and MATCHES operator (based on regex) should work properly in your case:

$column1$  MATCHES ".+" => $column1$
TRUE => $column2$

It says if there is something in column1 (blank cell excluded) take it, otherwise take value from column2.

Br,
Ivan

Many thanks to all,

now I have the results I was looking for. Which you all a happy easter.

Br,
Jürgen

1 Like

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