Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ciaran_mcgowan
Partner - Creator III
Partner - Creator III

Linking Dates/Date Ranges in Separate Tables

Hi,

I have 2 tables; 1 that contain Sales data and another that contains Employee data. They are linked by the Department name the employee works in/sale was made in. Each employee has a Start/End Date for their department and if they move department, the End Date is filled in and another record for them is created. A master calender was created from the date a Sale was made.

If I select a Date, the information about the Sale appears but it also lists every employee that every worked for the department the sale occurred in. Is there a way to only show the employees that where employed on the date a sale was made. And similarly, by selecting an employee, show all the sales that occurred when they were a member of that department.

In other words: Start Date > Sales Date > End Date = Show available Sales/Employees

Thanks.

3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Take a look at IntervalMatch in the Ref Guide. I think that will do what you want.

-Rob

ciaran_mcgowan
Partner - Creator III
Partner - Creator III
Author

Hi Rob,

Thanks for your reply.

I already tried that but it just created an infinite loop between the Sales table, the Employee table and the Master Calendar table (which is linked to the Sales table by SalesDate).

danieloberbilli
Specialist II
Specialist II

Hi Ciaran,

Please have a look at the qvw attached.

This is definitely not best practise, as first of all the join will probably be too slow for big tables. Nevertheless it should help to get an idea how to approach the problem.

To avoid the join please have a look at the different methods regarding 'slowly changing dimensions',

Kind Regards

Daniel