Jump to content

left single match join, returning the row based on a condition

Stacey McWaters

Recommended Posts


I have a left single match join in spotfire and there is more than one rowin right table. I know it is automatically set to return the first row, however i am needing it to return the appropriate row based on a condition. I'm working in Spotfire 10.


There is a work order table and a HR records table, the rows are matched by 'personel ID' column. The HR table can have multiple results for the personel ID so i need it to look in the 'Current Role' column and return the row that has 'Y' in it. If there is no match for 'Y' in that column then the first row is to be returned.


Hopefully someone has an answer for this!

Thanks in advance

Link to comment
Share on other sites

If you want to do such a conditional join with the regular join options in Spotfire you have to prepare your datasets. For example, in your HR table you could create a calculated column that clearly defines which rows should be used for the join. Then you could create a second HR table that is based on the first one but with using a transformation to filter to the rows that should be used for the join. Now you can use a regular left join to add this second HR table to your work order table. There will be multiple options to use combinations of transformations (join + filtering) to get to the desired result.
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...