IntervalMatch and Slowly Changing Dimensions

[hic]

• 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

[mikefromoke]

Thank you...

It works!

great!

regards

mike

[alex_millan]

Thanks, very helpful

[#4]

Nicely explained.

[#5]

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
;

[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

[#7]

Hi Omar,

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

Dan

[taha_mansoor]

@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

[dwforest]

 links are broken after moving to new format 😞

[Daniel29195]

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,