CODEWITHSUNDEEP
joinspotfire
AB123
AB123

Reputation: 163

Spotfire: Joining two tables - alternative to outer join

I need help efficiently joining two tables in Spotfire. The first table shows tasks executed by employees on certain dates, while the second table shows the role/function of employees which can change due to promotions. I want to add the role of the employee on that specific date to the first table. The most logical solution I can think of is an outer join and then filter out the irrelevant lines, but with both tables having roughly 1M rows, this creates a massive number of lines before filtering. Are there better alternatives?

Example:

Task Employee Date
A Steve 01/01/2022
B Steve 01/07/2022
Employee Role Start date
Steve Engineer 01/01/2021
Steve Team lead 01/04/2022

Desired end result:

Task Employee Date Role
A Steve 01/01/2022 Engineer
B Steve 01/07/2022 Team lead

Upvotes: 0

Views: 135

Answers (1)

Akina
Akina

Reputation: 42728

SELECT t1.*,
       ( SELECT role
         FROM t2
         WHERE t1.employee = t2.employee
           AND t1.date >= t2.startdate 
         ORDER BY t2.startdate DESC LIMIT 1 ) role
FROM t1

Upvotes: 0

Related Questions