Data manipulation in multi-column

[Teodor]

Hi, everybody!
I have seen similar questions on the forum, but them could not solve my problem. I have a few questions:
The first one:
There is a table of data that I read from excel. It looks like this:

The table is large with many columns. As you can see, each empty cell has a “NULL” record. I would like to get rid of it. It’s like a command like in an excel: Find&Replace or something like in R dplyr/stringr solutions.
I tried “String Manipulation (Multi Column)” like this: replace($$CURRENTCOLUMN$$,“NULL” ,“” ) , but without success. I got error:

 WARN  String Manipulation (Multi Column) 3:19       failed to apply settings: org.knime.ext.sun.nodes.script.compile.CompilationFailedException: Unable to compile expression
ERROR at line 66
The method replace(java.lang.String, java.lang.String, java.lang.String) in the type org.knime.base.node.preproc.stringmanipulation.manipulator.ReplaceManipulator is not applicable for the arguments (java.lang.Double, java.lang.String, java.lang.String)
  Line : 65    public java.lang.String internalEvaluate() throws Abort {
  Line : 66  return replace(__variable_0,"NULL" ,"" );

If I use: replace($Date of death$,“NULL” ,“” ) it’s work, but I want apply that to all columns, not only one or manually insert one by one.

Second question related to date, in my data I have dates like this “dd/mm/YYYY” (e.g. 04/07/2016) so in KNIME its read like this: 2016-07-04T00:00, so can you do something about it? if i write down after *.csv manipulations, the dates will break. if i write down excel again, its fine.

Thanks you.

[elsamuel]

For your first issue, the error message tells you what’s wrong:

The method replace(java.lang.String, java.lang.String, java.lang.String) in the type org.knime.base.node.preproc.stringmanipulation.manipulator.ReplaceManipulator is not applicable for the arguments (java.lang.Double, java.lang.String, java.lang.String)

The first argument of the “replace” function must be a string because the String Manipulation node only works on string columns. It will not work for columns of double type

For your second issue, how are you telling KNIME to format the date? This is completely customizable.

[ipazin]

Hello @Teodor,

and welcome to KNIME Community!

Have you managed to figure it out? You can use following expression to cast any non string column to string and replace NULL with empty string.
replace(string($$CURRENTCOLUMN$$), "NULL" , "" )

But keep in mind, it doesn’t make sense to apply such expression to numeric columns as NULL can not be in numeric column. Plus your numeric columns will be converted to string columns.

Regarding the date format. What you see (2016-07-04T00:00) is standard format for Date&Time column in KNIME. This allows you to work (manipulate, extract) using date and time nodes (e.g. Extract Date&Time Fields, Date&Time Difference…). To get your desired format you can use Date&Time to String node right after data import or before data export in case you need to perform some manipulations with it (I suggest getting data into shape just before export).

Hope this helps!

Br,
Ivan