Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

A common problem in business intelligence is when you want to link a number to a range. It could be that you have a date in one table and an interval – a “From” date and a “To” date – in another table, and you want to link the two tables. In SQL, you would probably join them using a BETWEEN clause in the comparison.

 

Events and Intervals with links.png

 

But how do you solve this in QlikView, where you should avoid joins?

 

The answer is to use IntervalMatch.

 

IntervalMatch is a prefix that can be put in front of either a Load or a SELECT statement. The Load/SELECT statement needs to contain two fields only: the “From” and the “To” fields defining the intervals. The IntervalMatch will generate all the combinations between the loaded intervals and a previously loaded numeric field.

 

Typically, you would first load the table with the individual numbers (The Events), then the table with the Intervals, and finally an intervalmatch that creates a third table that bridges the two first tables.

 

Events:
Load * From Events;

Intervals:
Load * From Intervals;

IntervalMatch:
IntervalMatch (Date)
Load distinct FromDate, ToDate resident Intervals;

 

Intervalmatch.png

 

The resulting data model contains three tables:

  1. The Events table that contains exactly one record per event.
  2. The Intervals table that contains exactly one record per interval.
  3. The IntervalMatch table that contains exactly one record per combination of event and interval, and that links the two previous tables.

 

Note that this means that an event may belong to several intervals, if the intervals are overlapping. And an interval can of course have several events belonging to it.

 

This data model is optimal, in the sense that it is normalized and compact. All QlikView calculations operating on these tables e.g. Count(EventID) will work and will be evaluated correctly. This means that it is not necessary to join the intervalmatch table onto one of the original tables. Joining it onto another table may even cause QlikView to calculate aggregations incorrectly, since the join can change the number of records in a table.

 

Further, the data model contains a composite key (the FromDate and ToDate fields) which will manifest itself as a QlikView synthetic key. But have no fear. This synthetic key should be there; not only is it correct, but it is also optimal given the data model. You do not need to remove it.

 

IntervalMatch can also be used with an additional key between the tables – i.e. when you have Slowly Changing Dimensions. But more about that in a later post.

 

HIC

 

For more on IntervalMatch and some script examples, see the technical brief IntervalMatch and Slowly Changing Dimensions.

41 Comments
Marcio_Campestrini
Specialist
Specialist

As usual another great post.

Thanks HIC.

0 Likes
9,437 Views
Not applicable

great post.

I have created another document with examples and alternatives for interval match

intevalMatch.pdf

0 Likes
9,437 Views
Not applicable

@Henric..I have same kind of request can you help us?

I have Transaction data and Discount tiers. I want to match both and would like to have desired results as described below..

Thanks for your help.

Transaction Data:

C_YearC_QtrDateProductCustomer NumberQuantityMember Type
2014412/1/2014Apple1000140AAA
2014412/1/2014Orange1000155AAA
2014412/1/2014Banana1000175AAA
2014412/1/2014Apple1000260BBB
2014412/1/2014Orange1000263BBB
2014412/1/2014Banana1000244BBB
2014412/1/2014Apple1000349CCC

Discount Tiers:

C_YearC_QtrProductDiscount_TypeMember_TypeTierStartEndDiscount%
20144AppleGAAAG_Tier_00340
20144AppleGAAAG_Tier_13549.91
20144AppleGAAAG_Tier_25064.992
20144AppleGAAAG_Tier_3659999993
20144AppleGBBBG_Tier_13549.91
20144AppleGBBBG_Tier_25064.992
20144AppleGBBBG_Tier_3659999993
20144AppleGCCCG_Tier_110252
20144AppleGCCCG_Tier_226503

Desired output:

C_YearC_QtrProductCustomer NumberMember TypeQuantity (Sum of Quarter)Discount_TypeTierDiscount%
20144Apple10001AAA80GG_Tier_33
20144Orange10001AAA150GG_Tier_33
20144Banana10001AAA110GG_Tier_33
20144Apple10002BBB120GG_Tier_33
20144Orange10002BBB88GG_Tier_33
20144Banana10002BBB126GG_Tier_33
20144Apple10003CCC98GG_Tier_33

I placed complete file in my question. Please let me know if you need any additional info.

Need help in computing Discounts based on Min and Max limits (by Customer)

0 Likes
9,437 Views
sujeetsingh
Master III
Master III

A best way to explain complex things

0 Likes
9,437 Views
Not applicable

Hi Henric,

I would like to generate a gantt chart or histogram "like" to be able to have visibility on the duration of certain events, but the only date I have is in the form of mm/dd/yyyy hh:mm:ss which correspond to event-start, example:

  • user  project  time                event
  • ----    -------    -----------------  ----------------
  • xyz  12345    01/01/01 10:10:10  project started
  • xyz  12345    01/01/01 11:11:11   project script ran
  • xyz  12345    03/01/01 01:01:01  project hold

the number of different events is around 27, but not all events are part of every project, which can have as many as needed, even if the event repeats a later time. How can I graphically represent the events per project number where I am able to see the time it took each event? Thanks in advance for all the help you can provide, i have been struggling with this representation for a long time now 😞

Carlos

BTW, I am using the Calendar Script that Rob Wunderlich wrote (Cookbook).

0 Likes
9,429 Views
hic
Former Employee
Former Employee

If one event starts right after the previous one ends, then you can use the start time of the second event to define the end time of the first. See Creating a Date Interval from a Single Date

HIC

0 Likes
9,429 Views
Not applicable

Hi Henric,

Thank you for the prompt response to my questoin! I tried the method you suggested and it seems to work to create the dates interval, but I am more concern about how to get the time, we have many events during the same day that belong to the same project, for which we need to record and report.  Do you know how can I expand the Interval to use both variables (date & Time)? Thanks!!! Carlos

0 Likes
9,429 Views
hic
Former Employee
Former Employee

If you have Date and Time as two separate (and correctly interpreted) fields, you can simply add them to get the correct timestamp. You probably want to wrap in a Timestamp function also, to get the correct formatting.

Hence:

Timestamp(Date + Time) as StartTime

If you want to combine this with creating the end times, I suggest something along the lines of

Load

   Timestamp(Date + Time) as StartTime,

   Timestamp(Peek(StartTime)-0.00000001) as EndTime,

   ...

   Resident Table

   Order By Date Desc, Time Desc;

HIC

0 Likes
9,429 Views
Not applicable

Hi Henric,

Once again, thanks for your fast response to my question!  I really appreciate it !!!

My StartTime field combines both date & time in the following format: "2/1/15 5:32 PM" - I tried to used the command "Peek(StartTime)-0.00000001) as EndTime," but i believe I'm not using it right because I get an error while trying to load the data.

The following error occurred:

ErrorSource: Microsoft Access Database Engine, ErrorMsg: Undefined function 'Peek' in expression.

Again, thank you for all the help you can provide me !

Carlos

0 Likes
9,429 Views
hic
Former Employee
Former Employee

The error message shows that you have written the Peek() function inside the SELECT statement. This is not allowed. Use it in a Load statement instead. The structure may be

Load ... ;

SQL SELECT ... FROM ... ;

The Load statement is interpreted by QlikView, and the SELECT statement is interpreted by the ODBC driver (that knows nothing of Peek()). See Preceding Load.

Further, it sounds as if you perhaps havn't interpreted your Start time correctly. To do this, you need to use the TimeStamp#() function. See The Date Function and Get the Dates Right for an explanation of interpretation functions.

9,429 Views