hi,
i have 2 tables that i want to join with a condition.
1ST table is FACT and contains sales transaction
2ND table is HR and contains the sell agents info
the problem, sell agent can switch teams, so the table contains more the 1 row for those agents.
i would like to match the sell from the FACT table, to the agent from the HR table, corresponding the team he was into that period.
example:
sell date : 1/5/2022
LOAD * INLINE [
agent_name, team, start_date,
agent A, TEAM A, 15/5/2021
agent A, TEAM B, 1/1/2023
]
the join will math sell date 1/5/2022 with agent A from TEAM A. and sells after 1/1/2023 will math to TEAM B.
agent may change team without limit.
thanks.
