topic Re: Intervalmatch-type problem in QlikView

Intervalmatch-type problem

alexis — Thu, 13 Nov 2014 18:51:14 GMT

I have simplified my problem with this following example:

I have a Calendar (as above) and a file (called Usage here) that has records that contain a StartDate an EndDate and a SubscriberKey. The 2 tables need to be disjointed (remember this is a small part of a very large application).

I need to see a distribution (by Month-Year) of active subscribers ( i.e. count(distint (SubscriberKey))) - the way that I ascertain that a subscriber is active for a specific month is to see whether they have a StartDate that is either inside or before the month and an EndDate that is no less than the beginning of the month.

As an example, active subscribers for the month of "May 2014" are those that have a StartDate that is less than or equal 31/5/2014 and an EndDate that is greater or equal 1/5/2014. I have already ensured that the EndDate is greater than the StartDate.

I have attached a demo file that has some data and the above structure that can be used. I also attach the 2 QVDs that make up the data.

This is not a classic intervalmatch scenario (I don't think) as I am not charting by the Start/EndDate intervals but merely using these fields to establish inclusion.

Any assistance would be much appreciated.

Alexis

Re: Intervalmatch-type problem

Gysbert_Wassenaar — Fri, 14 Nov 2014 09:27:55 GMT

Your TestUsage.QVD file seems to be corrupt. I can't load it.

What you can try is generate records for every day for the interval between Start and End date:

Usage:

LOAD UsageKey,

SubscriberKey,

StartDate,

num(StartDate) As StartDateInt,

EndDate,

num(EndDate) As EndDateInt,

Status,

StartDate + IterNo() -1 as Date

FROM comm142040.QVD (qvd)

WHILE StartDate + IterNo() < EndDate;

Re: Intervalmatch-type problem

alexis — Fri, 14 Nov 2014 10:19:58 GMT

Hi Gysbert

You are right about the corruption - I posted this with valid QVDs etc at:

Intervalmatch() or Date comparison? | Qlik Community

Do you mind putting your response to that thread please or something else now that you have valid data files!!

Thanks for responding

Alexis

Re: Intervalmatch-type problem

alexis — Fri, 14 Nov 2014 14:59:42 GMT

Hi Gysbert

Tried your suggestion but stopped the process after 70 million records got created....

Thanks

Alexis