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

[Stacey McWaters]

Hi,

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.

e.g

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

[Fabian Duerr]

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.