Qlik Community

Ask a Question

Documents

QlikView documentation and resources.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY

IntervalMatch and Slowly Changing Dimensions

Henric_Cronström

IntervalMatch and Slowly Changing Dimensions

  • What techniques are there to match a number with an interval?
  • How does the IntervalMatch prefix work?
  • Should I join the interval match table with the event table?
  • How do I generate an interval from a single change date?
  • What is a Slowly Changing Dimension?
  • How do I model a multi-level Slowly Changing Dimension?

These questions and others are answered in this Technical Brief.

Thank you Barry for good discussions.

See also the following blog posts

IntervalMatch

Creating a Date Interval from a Single Date

Creating Reference Dates for Intervals

Slowly Changing Dimensions

HIC


Attachments
Comments
mikefromoke
Creator II
Creator II

Thank you...

It works!

great!

regards

mike

0 Likes
alex_millan
Creator III
Creator III

Thanks, very helpful

0 Likes
Not applicable

Nicely explained.

0 Likes
Not applicable

Hi hic and Community,

I did some practic according the method what I found in the PDF, „Method 2: Using a While loop creating enumerable values”. Tried it with minutes instead of days but wasn’t able to implement it, there is an issue with the BridgeTable.

Thanks for your help in advance,

Daniel                      

Event:
LOAD
*
Inline [
Time, Event, Comment
01:01, E1, Line stop
01:06, E2, Line restart 50%
04:15, E3, Line speed 100%
08:00, E4, Start of shift 2
11:43, E5, End of production
]
;

Intervals:
LOAD *,
Recno() as IntervalID
INLINE [
Start, End, Order
01:00, 01:16, A,
02:30, 07:58, B,
03:04, 10:27, C,
07:23, 11:43, D,
17:00, 18:00, E,
]
;

BridgeTable:
LOAD  Distinct
*
Where Exists(Time);
LOAD
Time((Start+(Iterno()-1)/1440)) as Time,
IntervalID
Resident Intervals
While Time((Start+(Iterno()-1)/1440)) <= End
;

0 Likes
OmarBenSalem
Partner
Partner

Hi all,

Can you please assist me on this?

Slowly Changing dimension - IntervalMatch (duplicated date problem

I used IntervalMatch for SCD but my date has been duplicated !

I'm really confused !

Thanks

0 Likes
Not applicable

Hi Omar,

maybe you can try DISTINCT when you are creating the bridge table.

Dan

0 Likes
taha_mansoor
Contributor III
Contributor III

@Henric_Cronström

Hi Henric,

Excellent blog, as usual, I just went through the examples and I found that Method 1 in your example is working for half-open intervals perfectly fine. My code for intervals table is as follows:

 

Intervals:

LOAD * INLINE [
Interval_ID, Interval_Start, Interval_End, Interval_Name
1, 0, 999.99, 0-1000
2, 1000, 1999.99, 1000-2000
3, 2000, 2999.99, 2000-3000
4, 3000, 3999.99, 3000-4000
5, 4000, 4999.99, 4000-5000
6, 5000, 5999.99, 5000-6000
7, 6000, 6999.99, 6000-7000
8, 7000, 7999.99, 7000-8000
9, 8000, 8999.99, 8000-9000
10, 9000, , 9000 Above
] ;

 

So, I am leaving the upper-bound blank and its working fine for me. So, the limitation in the pdf - pg.no 5 - 2nd paragraph is no more. What do you say ?

Regards,

Taha

0 Likes
dwforest
Specialist II
Specialist II

 links are broken after moving to new format 😞

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-06-03 07:51 AM
Updated by: