Hello everyone! I’m new to QV, and need a suggestion for the following problem:
I have a table DimTime that I’m loading from one datasource, here is the structure:
Columns:
Day (PK)
Week_Start
Week_End
Month
Quarter
Year
Now, I have my Orders table I’m bringing from another datasource, and here is the structure:
OrderID,
StartDate,
Stop Date,
Price
Our orders “run” for a period of time, paying the us the same fixed price each week they are running, so that’s why we have OrderID, StartDate and EndDate.
Now, in order to calculate Revenue for each particular week I need to see what orders were running during that week, in SQL I would do something like:
Select
SUM(Price)
From Orders
Where StartDate <= [Last Day of Week] and (StopDate > [Last Day Of Week>] or StopDate = ‘0000-00-00’)
That will give me all the orders that started running anytime prior or during the week, and were still running after the end of this week.
Because of that specific, if in QlikView I join my DimTime and Orders table on Day and StartDate, I won’t get the needed result. For each week I need to grab all orders that that started running before or during this week, and continued running after the week ended.
It would be great if anyone could point me in the right direction, it’d be just great.
I'm not following exactly, but I believe that "intervalmatch" is the right direction. Have a look in the help text for that, and let me know if you need more help. I suspect you'll be able to figure it out from there.
Thank you both for the suggestions. I'll try to see what I can come up with using your suggestions. Will let you know what worked once I come up with the solution.
