Split rows based on Date and Time

Hi all,

I would like to split my rows based on a column with different dates.
and what I want to do is to split the rows which is prior than today(or a given date) and later than today.
I have tried Date & time rule based filter, but what I want is a splitter.
Please kindly share if any other nodes I can try.
Thanks!

Hi @Milly_Lin and welcome to the Knime Community.

Could you share some sample data and also show us what the expected results would be for the sample data, just to make sure we understood what you are trying to do?

Hi,

This can be done in several ways using a variety of nodes. Main point is a rule evaluation of the subject date compared to today.

(Column Expression example)

if (column("date") < today()) {
    "Left"
} else if (column("date") > today()) {
    "Right"
}

Example using this code at the time of writing (2022-05-19):
image

Hello Milly_Lin,
I am not sure if I understood your question correctly. Can you update your question with sample values and output ?

From what I understood,
I think you can use “date time variable” node with use execution date option and link it rule engine/string manipulator/column expression where you can append a flag column with the > today logic and with “row filter” node you can get two outputs

Hi,

Thanks for your reply. the example is like below. If today is 2022/5/20.
I would like to split the rows based on the date and will combined to one table again after several other nodes.

擷取

Thanks!

Hi, Thanks for your reply, I put the example as above.

Hi @Milly_Lin , thank you for the sample data and explanation.

Indeed, you need a Splitter node instead of a Filter node (unless you use 2 Filter nodes that are mutually exclusive, but that’s inefficient).

You can use the Rule-based Row Splitter node:

You can also dynamically get the current date whenever you run the workflow, using the Create Date&Time Range node:

You can configure like this:

I know the title of the post says “based on Date and Time”, but looking at your sample data, it looks like it depends only on date, so I configured the node to produce a Date value instead of a Date&Time.

By using the execution date&time, it will give the current date. In the event you want to split at some other date, you can specify the date instead of using the execution date&time.

Something like this would do for you:
image

It’s still 2022-05-19 here, so that’s my current date:
image

I have the same sample data as you:
image

And I used this rule in the Rule-based Row Splitter:
$Date$ <= $${SDate}$$ => TRUE

where $Date$ is my Date column, and $${SDate}$$ is the variable “Date” holding the current date value.

I set it so that whatever satisfies the rule goes in the first table, and the rest in the 2nd table as per what you want.

Results:
First output (Top):
image

Second output (Bottom):
image

Here’s the workflow: Split rows based on date.knwf (11.2 KB)

3 Likes

this solution looks good! thanks!!

1 Like

You are most welcome @Milly_Lin

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