Joining two tables on a date range

[#1]

Hi all. I have a complex problem that I'm having trouble with. So I have three tables, invoices, work items, and customers. Customers have a customer ID. Invoices have an invoice date, customer ID, and amount. Work items have a customer ID, start date, and end date. There is no work item ID, as this is a derived dimension.

I'm trying to tie invoices to work items, by checking if the invoice date is between the start and end date of the work item for a given customer.

I've found one solution for this. but it's terribly inefficient. I generate a date key in a mapping load for every day between the start and end dates for the work items, then generate a date key for the invoice date, and then link the invoice to the work item via the date keys. This mapping load ends up being 60 million + rows, and takes 20 minutes to load.

So my question is, is there a more elegant way to join one table to another via a date, where you only have a date range?

[MarcoWedel]

Yes, IntervalMatch

[#3]

So I did look into intervalmatch. The main issue I have is it's not just a simple date comparison. I also have the customer ID as well. I need to match the invoice date to the work item start/end range over each customer ID. So I might need to create a composite key. Can intervalmatch do this?

Also, I'm concerned about the performance using a synthetic key. I've always learned that synthetic keys are bad.

So I've attached an example QVW, and as you can see, intervalmatch doesn't work because it's not a simple date range, there's the extra customer ID that needs to be used to split it up.

[MarcoWedel]

Yes, there is an extended IntervalMatch syntax with additional parameters/keyfields for slowly changing dimensions and

No, Synthetic Keys are not always bad:

Should We Stop Worrying and Love the Synthetic Key?

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

and particularly:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

where Henric Cronström states that

"Further, the data model contains a composite key (the FromDate and ToDate fields) which will manifest itself as a QlikView synthetic key. But have no fear. This synthetic key should be there; not only is it correct, but it is also optimal given the data model. You do not need to remove it."

hope this helps

regards

Marco