Is bidirectional intervalmatch possible?

[Anonymous]

Hello,

I understand how intevalmatch works but my problem is when the value overlaps two intervals. I have created my Bridge Table and and join to remove synthetic keys but this only gives half the solution.

We have customers with Plans and Features on those plans.  The features can overlap from on plan to the next.

My interval match shows the feature FBASIC when the plan P800 is selected because the fStartDt is between the plans pStartDt and pEndDt but feature overlaps into the next plan P1500.

In some fashion am I to do a second pass on intervalmatch going from the plans pStartDt between the features dates?

Plans     

Customer	SKU	Group	ProductDesc	pStartDt	pEndDt
1234	P800	Plans	800 Plan	7/25/2015	8/4/2015
1234	P1500	Plans	1500 Plan	8/4/2015	9/30/2015

Features      

Customer	SKU	Group	ProductDesc	fStartDt	fEndDt
1234	FBASIC	Features	Basic Add-On	7/25/2015	9/30/2015

Thank you

[swuehl]

Maybe you want to split your Feature into two sections, one mapping to P800 and one two P1500 with different date ranges? Something like this?

edit: If so, have a look at HIC's technical brief, page 17ff

IntervalMatch and Slowly Changing Dimensions

[Anonymous]

Swuehl,

So thinking about what you said I would need to use the end date from the Features and interval match that on a separate bridge between the Plan  dates?

I could possibly see something like that working in this scenario but if a Feature jumped the duration of 3 plans the the middle would still be left out as neither the fStartDt or fEndDt would fall in between the Plan dates.

I really think the solution has to do somehow with henric cronström‌ post where he talks about Interval Partitioning https://community.qlik.com/docs/DOC-4310?et=watches.email.document_comment#comment-36858

Somehow creating new Start and End dates.  His example is a bit different from my scenario though.

[swuehl]

Your answer and my edit seems to have overlapped (or maybe your post was in moderation), but yes, something like Henric demonstrates in the tec brief page 17 ff could work, IMHO.

[rwunderlich]

Darrin,

I would think you would want to create a Date key to link the Plans ans Feature Tables so on any specific day you could identify what Plans/Feature were in effect for that day? Something like this:

Plans:

LOAD *

  ,date(pStartDt + IterNo()-1) as Date

INLINE [  

Customer, PlanSKU, PlanGroup, PlanProductDesc, pStartDt, pEndDt

1234, P800, Plans, 800 Plan, 7/25/2015, 8/4/2015

1234, P1500 ,Plans, 1500 Plan, 8/4/2015, 9/30/2015

]

WHILE pStartDt + IterNo()-1 <= pEndDt

;

Features:

LOAD *

  ,date(fStartDt + IterNo()-1) as Date

INLINE [  

Customer, FeatureSKU, FeatureGroup, FeatureProductDesc, fStartDt, fEndDt

1234,FBASIC,Features,Basic Add-On,7/25/2015,9/30/2015

]

WHILE fStartDt + IterNo()-1 <= fEndDt

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[swuehl · Accepted solution]

Attached is the adaption of HICs approach, using indeed a bidirectional intervalmatch.

A little more complex than Rob's solution, but avoiding creation of records for all days in the intervals.

[Anonymous]

Swuehl,

Thank you very much.  The SubIntervals was what I was needing. 

To take it a step further I then took the Features and had to then inervalmatch them a second pass on the plans and join them to deal with Nulls created based on the Dimension levels I needed.

Thank you.

[Anonymous]

Rob,

When I tried that it gave me so many combinations and took a really long time to run that it was not feasible.

Unless I was doing something wrong.

Thanks

[Anonymous]

Swuehl,

There is a problem with this that I wonder if you can help with in what you sent.

The top and bottom intervals are not displayed when using the sub-intervals.

I have modified the dates so that the Features end prior to the plans which is a scenario with our data.

You will notice the last plan range is 8/5/2015 to 9/30/2015.  The feature below ends on 9/20/2015.  Is there some way if I were to select the date of 9/27/2015 it would only show the Plan interval of 9/20/2015 to 9/30/2015.

PlanID	Customer	pSKU	pStartDt	pEndDt	pGroup	pProductDesc
1234|8/5/2015|9/30/2015	1234	P1500	8/5/2015	9/30/2015	Plans	1500 Plan

FeatureID	Customer	fSKU	fStartDt	fEndDt	fGroup	fProductDesc
1234|8/1/2015|9/20/2015	1234	FULTIMATE	8/1/2015	9/20/2015	Features	Ultimate Add-On

Thank you.

[swuehl]

I see. I think the problem is with INTERVALMATCHing closed inclusive intervals.

I tried to correct this with correcting the end dates for the INTERVALMATCH.

Does the attached sample matches your expectation?