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]

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

[Kirsten Smith (she/her)]

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]

[Gaia Paolini]

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.

 

Gaia

[James McDonald]

Hi kirsten, thanks for this. I have Spotfire Analyst 7.6.1, is it possible to simply upgrade to spotfire X I can't see anywhere to do it online.

 

 

[Kirsten Smith (she/her)]

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

 

https://edelivery.tibco.com

 

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.