Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
darrin_pilkingt
Contributor II

Interval match - interval partitioning is not working as expected

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.

   

PlanSKUPlanStartDtPlanEndDt
Plan11/11/20131/14/2013
Plan21/15/201312/15/2013
Plan312/15/20135/15/2015
Plan45/15/2015

   

FeatureSKUFeatureStartDtFeatureEndDt
Feature11/11/20145/15/2015
Feature23/15/20155/15/2015
Feature33/15/20155/15/2015
Feature412/15/20135/15/2015
Feature55/15/2015
Feature62/18/2013
Feature75/15/2015

Thank you for your help.

1 Solution

Accepted Solutions
marcus_malinow
Valued Contributor III

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

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

2 Replies
marcus_malinow
Valued Contributor III

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

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

darrin_pilkingt
Contributor II

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

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

:

PlanSKUPlanStartDtPlanEndDt
Plan11/11/20131/14/2013
Plan21/15/201312/15/2013
Plan312/15/20135/15/2015
Plan45/15/201512/31/2016

Thanks

Community Browser