Thanks again all - so I think I’ve arrived at a solution that mixes @takbb’s python-based and @mlauber71’s database-based join approaches. However, myself being neither an expert at either python nor databases, I’m hoping to get a hand in seeing why I’m only getting 2 rows out of 100K+ in my output.
First, here’s the workflow excerpt, which simply takes the main data table from the left side, and the scaffold dates on the right side:
Here’s the python script:
import knime.scripting.io as knio
import pandas as pd
# data from KNIME to Python (pandas)
df = knio.input_tables[0].to_pandas()
df1 = knio.input_tables[1].to_pandas()
# Convert KnimePandasExtensionArray to regular Series
df['Contract Date'] = pd.to_datetime(df['Contract Date'])
df['End of Life Date'] = pd.to_datetime(df['End of Life Date'])
df1['Scaffold Date'] = pd.to_datetime(df1['Scaffold Date'])
# Perform the left join with filtered Table 1 and Table 2 using pandas merge
output_table = pd.concat([df, df1], axis=1)
output_table = output_table[
(output_table['Contract Date'] <= output_table['Scaffold Date']) &
(output_table['End of Life Date'] >= output_table['Scaffold Date'])
]
# data from python (pandas) to KNIME
knio.output_tables[0] = knio.Table.from_pandas(output_table)
Here’s the output of only two rows:
Here’s the input excerpt (of +119K rows):
My suspicion is perhaps the python code is doing this across the entire column, irrespective of the Listing ID (not pictured), and thus of the 119K rows, it finds only 2 rows. Each row has a Listing ID field that is a unique identifier, and at this point in the ETL a Listing ID won’t be shown twice (so only 1 row for each).