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

Is bidirectional intervalmatch possible?

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     

CustomerSKUGroupProductDescpStartDtpEndDt
1234P800Plans800 Plan7/25/20158/4/2015
1234P1500Plans1500 Plan8/4/2015

9/30/2015

Features      

CustomerSKUGroupProductDescfStartDtfEndDt
1234FBASICFeaturesBasic Add-On7/25/20159/30/2015

Thank you

1 Solution

Accepted Solutions
MVP
MVP

Re: Is bidirectional intervalmatch possible?

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.

9 Replies
MVP
MVP

Re: Is bidirectional intervalmatch possible?

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

darrin_pilkingt
Contributor II

Re: Is bidirectional intervalmatch possible?

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.

MVP
MVP

Re: Is bidirectional intervalmatch possible?

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.

Re: Is bidirectional intervalmatch possible?

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

MVP
MVP

Re: Is bidirectional intervalmatch possible?

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.

darrin_pilkingt
Contributor II

Re: Is bidirectional intervalmatch possible?

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.

darrin_pilkingt
Contributor II

Re: Is bidirectional intervalmatch possible?

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

darrin_pilkingt
Contributor II

Re: Is bidirectional intervalmatch possible?

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.

       

PlanIDCustomerpSKUpStartDtpEndDtpGrouppProductDesc
1234|8/5/2015|9/30/20151234P15008/5/20159/30/2015Plans1500 Plan

       

FeatureIDCustomerfSKUfStartDtfEndDtfGroupfProductDesc
1234|8/1/2015|9/20/20151234FULTIMATE8/1/20159/20/2015FeaturesUltimate Add-On

Thank you.

MVP
MVP

Re: Is bidirectional intervalmatch possible?

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?

Community Browser