Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

IntervalMatch and Slowly Changing Dimensions

cancel
Showing results for 
Search instead for 
Did you mean: 
hic
Former Employee
Former Employee

IntervalMatch and Slowly Changing Dimensions

Last Update:

Jun 3, 2013 7:51:38 AM

Updated By:

hic

Created date:

Jun 3, 2013 7:51:38 AM

Attachments
  • 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


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

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
Creator
Creator

@hic

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
Daniel29195
Contributor III
Contributor III

Hello , 

In new to qliksense so can t tell if my question would make sense.

 

What if i have 2 tables with the same column field name ( example orderdate), 

And this this field is the one used in intervalmatch ( intervalmatch(orderdate) , 

 

Then the concept will be applied on both tables right ?  Or the most recent loaded table ? 

 

another question, should i always use join with intervalmatch ? 

Thanks for any help,

 

 

Version history
Last update:
‎2013-06-03 07:51 AM
Updated by:
Former Employee