topic Linking Dates/Date Ranges in Separate Tables in QlikView

Linking Dates/Date Ranges in Separate Tables

ciaran_mcgowan — Wed, 10 Sep 2014 23:07:51 GMT

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.

Re: Linking Dates/Date Ranges in Separate Tables

rwunderlich — Thu, 11 Sep 2014 01:39:29 GMT

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

-Rob

Re: Linking Dates/Date Ranges in Separate Tables

ciaran_mcgowan — Thu, 11 Sep 2014 09:52:04 GMT

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).

Re: Linking Dates/Date Ranges in Separate Tables

danieloberbilli — Sat, 13 Sep 2014 12:32:53 GMT

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