Alternative to Cell splitter

[Adiwakar]

I have a need to split time from dates. I would have used the Extract date&time node but the dates and times are in some not so great formats. So I am forced to use the cell split. But there are a lot of date and time columns. The Cell Splitter node accepts only one column at a time per node and after these splits they have to go through joins. Is there a faster way to do this? any other node ?

[armingrudd]

Hi,

Please provide a sample of your dataset and an example of what you want as output.

[Adiwakar]

Hi,

The date formats are as follows:
2019-04-29T00:00:00.000 -> in column 1
2019-08-01 09:30:40.212 -> in column 2
2019-04-29T14:08:13.222 -> in column 3
2019-04-27T00:00:00.000 -> in column 4

From these types of date I was wondering if there was a single node where I could separate the date from the time and keep just the date. And perform this operation on all 4 columns using a single node.

Note: Each of the above timestamps are in a column of their own.

[quaeler]

A Column Expressions node and some regex will do it.

adi’s dates.knwf (10.4 KB)

[ipazin]

Hi there @Adiwakar,

Couple of comments:

• If I see good columns 1,3 and 4 have same format…
• Extract Date&Time node can only use one column at the time so you would need to use this node multiple times and that is what you want to avoid
• Node that can modify multiple columns at once and leave only date from Date&Time format is Modify Time with option Remove time.

So if you have type columns Date&Time use Modify Time node, otherwise (they are String type I guess) you can use Column Expressions node as suggested by @quaeler. Also if not familiar with regex you can use substring function instead as your date is always contained in first 10 characters (at least from your example).

Br,
Ivan

[Adiwakar]

Hi,

This seems to be working. Except for condition where the timestamp is missing or not present.
I tried using this
if (isMissing(column(“Col1”))!= null)
{
regexReplace(column(“Col1”),"\s.*" ,"");
}
else
{
“No Date”
}

But this did not work. I am getting the good old null pointer exception . Suggestions?

[quaeler]

Whenever you use a function, take note of its documentation which is listed to the right of the function list.

isMissing returns a boolean, so it will always return something non-null.

[Adiwakar]

Thanks. I see now. isMissing will return true or false which not null so it is always meeting the condition of !-null. Made the change to look for !=true. it worked. Thanks

[Andrew_Steel]

Hi @Adiwakar,

if there is mor than 1 column, and your samples shows the only different dat&time version, the String to Date&Time node with followed settings should help …

OK, it’s not a single node solution

Best regards
Andrew

[armingrudd]

Hi,

The solution provided by @quaeler is working fine but I think @Andrew_Steel has provided a more straightforward solution. To make @Andrew_Steel’s solution solve your issue, we have to select “Date” for the “New type” option and now all you want is done in a single node as you wish: