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?
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.
"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."
