most efficient way to return set from two tables where id match, and salesdate > effectivedate
Can someone tell me the best way in Qlikview to get those 2 tables to return the results in Table 3. In this scenario, the sales consultant changes the manager periodically and this is stored in Table 2. The sales records contain the consultant id, date, etc. The report should do analysis by consultant and the manager at the time the sale was recorded.
Table 1 Sales:
Table 2 ConsultantManagerHistory.
Consultantid, Effectivedate, Managerid
10, 01/01/2011, 100
10, 01/02/2011, 200
20, 01/01/2011, 300
Table 3: the sales records to return the consultant manager id based on the effective date from the consultant history