topic Re: How to perform a join on dates in QlikView

How to perform a join on dates

Tue, 24 May 2011 21:56:57 GMT

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.

How to perform a join on dates

johnw — Tue, 24 May 2011 22:21:10 GMT

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.

Re: How to perform a join on dates

Wed, 25 May 2011 01:30:55 GMT

I would suggest you create a report date on the result of your 2nd query. So you then join your calendar table to that unified report date.

ie.

OrderID,

StartDate,

EndDate,

Price,

ReportDate

The new field report date could be the run date of the reporting or period ending date. I'm sure you can use a sql function and today() to create it.

Re: How to perform a join on dates

Wed, 25 May 2011 15:34:48 GMT

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.

Re: How to perform a join on dates

Wed, 25 May 2011 15:36:26 GMT

Don't have the solution figured out yet, but working on both of your suggestions. Will post if/when come up with the solution. Thank you!