Filter table output based on another table's date

[jhandatx]

Greetings,

I have a challenge and I’m hoping KNIME can solve it. I have two tables:

Table 1

                                    Begin Date   End Date

Property A | Information | 12/01/2011 | 2/07/2011
Property B | Information | 2/01/2011 | 3/07/2011
Property C | Information | 3/01/2011 | 6/07/2011

Table 2
Exclude As of This Date
Property A | 11/01/2011
Property B | 02/04/2011

I would like to utilize Table 2 to filter Table 1 based on the date in Table 2. The output would be this report:

                                      Begin Date   End Date

Property A | Information | 12/01/2011 | 2/07/2011
Property C | Information | 3/01/2011 | 6/07/2011

Property B is excluded because the Date is after the Begin Date in Table A
Property A is included because Table A’s Begin Date is after Property A in Table B
Property C is included because it wasn’t in Table 2 to be excluded.

Any assistance would be greatly appreciated

Thank you and I look forward to your reply.

Signed,
John

[armingrudd]

Hi @jhandatx and welcome to the KNIME forum,

Use String Manipulation node to create rules based on table 2 (convert date column to standard date format) and then the Rule-based Row Filter (Dictionary) node will filter the rows based on those rules.

[ipazin]

Hi there @jhandatx,

I would use Cell Replacer to add date from table 2 into table 1. Then, if necessary, convert all dates to Date&Time column type in order to be able to compare dates in Rule-based Row Filter node and filter accordingly.

Br,
Ivan

[jhandatx]

Greetings Ivan,

Thank you very much!

[morpheus]

Hi @jhandatx,
one option to get straight forward is to use the column joiner node (left outer join) first and then the necessary nodes to generate the output.

Br,
Hermann