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.
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.
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
In Screenshot 2 I reversed Primary and Secondary.
Primary Column: Column2
Secondary Column: Column1
Append new Column: Column3
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.
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.
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.
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:
