Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Partner - Specialist III
Partner - Specialist III

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

View solution in original post

2 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

Anonymous
Not applicable
Author

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