Please can someone help me with adding columns to a table based on matching a single column in one table to relative data in multiple columns of another?
In "Table 1" I have 2 columns that I want to use for matching: Name, Date.
In "Table 2" I have three relevant columns for matching in addition to the columns that I want to add to Table 1: Name, Start Date, End Date.
I want to add columns from Table 2 to Table 1 by matching the the rows that have the same "Name" and where the "Date" in Table 1 is between "Start Date" and "End Date" in Table 2.
But I don't know how to do this, can someone help?
There could be multiple rows in Table1 where the date matches the same row in Table2. So I would create a new table out of the combinations, rather than adding columns to Table2.
What tools do you have at your disposal (Iron Python, TERR)? Are the columns to be copied from table2 to table1 fixed or can they change? It would help if you uploaded an example dataset.