topic Interval match - interval partitioning is not working as expected in QlikView

Interval match - interval partitioning is not working as expected

Anonymous — Wed, 27 May 2015 07:12:12 GMT

I have been reading through henric cronström‌ tech doc on interval match to solve an issue I have.

At any particular date selected I need to be able to identify the Plan and the Features a customer has active. Even if the Feature was active before the new plan if it's still active.

As in Feature6 below. It was active long before the current Plan4 but it should still display as it's still active.

I have customers that have Plans and Features, each with Start and End dates. So two table that look something like this. Customer can have only 1 Plan at a time but many Features on that Plan at any given time.

The problem I am running into is that when I attempt the interval partition my Subinterval table has no dates from any Plan or Feature that is currently active with no EndDt. The RangeMax and RangeMin expect values or return Null.

PlanSKU	PlanStartDt	PlanEndDt
Plan1	1/11/2013	1/14/2013
Plan2	1/15/2013	12/15/2013
Plan3	12/15/2013	5/15/2015
Plan4	5/15/2015

FeatureSKU	FeatureStartDt	FeatureEndDt
Feature1	1/11/2014	5/15/2015
Feature2	3/15/2015	5/15/2015
Feature3	3/15/2015	5/15/2015
Feature4	12/15/2013	5/15/2015
Feature5	5/15/2015
Feature6	2/18/2013
Feature7	5/15/2015

Thank you for your help.

Re: Interval match - interval partitioning is not working as expected

marcus_malinow — Wed, 27 May 2015 07:56:38 GMT

Darrin,

IntervalMatch won't work on your tables as they are - you'll need day level data.

To resolve this, I'd create a calendar table. Then I'd create a link table using Intervalmatch to match your Plans to the Calendar. Finally I'd use an Intervalmatch on this to join to the Features.

Hope this makes sense.

Marcus

Re: Interval match - interval partitioning is not working as expected

Anonymous — Wed, 27 May 2015 17:01:29 GMT

Marcus,

Thank you for the quick response. I think I was trying to over complicate how this would be built out. It did seem like since there were the two date ranges it needed the partition option but really it's a single table with a different type of record. Type = Plan or Feature. With the interval match on the Calendar table I can pick any date and it will pull the single Plan and any Features active at that time.

The only thing I had to do as I could not make this work was place a far out date for any currently active Plan or Feature. It works but seems like its not the best solution.

Example: Plan4 added future PlanEndDt

PlanSKU	PlanStartDt	PlanEndDt
Plan1	1/11/2013	1/14/2013
Plan2	1/15/2013	12/15/2013
Plan3	12/15/2013	5/15/2015
Plan4	5/15/2015	*12/31/2016*

Thanks