Jump to content
We've recently updated our Privacy Statement, available here. ×

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

James McDonald

Recommended Posts

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

Link to comment
Share on other sites

Starting with Spotfire X there is a new Transformation called 'Filter Rows' that should help with this issue. Insert the columns from Table 1 into Table 2 matching on [Name], then add a 'Filter Rows' Transformation to exclude rows where [Date] is not between [start Date] and [End Date]. That equation might look something like:


[Date] > [End Date] OR [Date] < [start Date]

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

If you are current on your maintenance you can download the latest version of Spotfire from our eDelivery website.




If you are using Spotfire Desktop you can search for that product. If you are using Spotfire Analyst then you will also need to upgrade your Spotfire Server to the current version.


Be advised that we did a major upgrade to the user interface so it will look very different when installed. There is a link to a short video that will describe how to navigate the new interface.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...