IntervalMatch

[hic]

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.

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;

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.

[Marcio_Campestrini]

As usual another great post.

Thanks HIC.

[#3]

great post.

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

intevalMatch.pdf

[#4]

@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_Year	C_Qtr	Date	Product	Customer Number	Quantity	Member Type
2014	4	12/1/2014	Apple	10001	40	AAA
2014	4	12/1/2014	Orange	10001	55	AAA
2014	4	12/1/2014	Banana	10001	75	AAA
2014	4	12/1/2014	Apple	10002	60	BBB
2014	4	12/1/2014	Orange	10002	63	BBB
2014	4	12/1/2014	Banana	10002	44	BBB
2014	4	12/1/2014	Apple	10003	49	CCC

Discount Tiers:

C_Year	C_Qtr	Product	Discount_Type	Member_Type	Tier	Start	End	Discount%
2014	4	Apple	G	AAA	G_Tier_0	0	34	0
2014	4	Apple	G	AAA	G_Tier_1	35	49.9	1
2014	4	Apple	G	AAA	G_Tier_2	50	64.99	2
2014	4	Apple	G	AAA	G_Tier_3	65	999999	3
2014	4	Apple	G	BBB	G_Tier_1	35	49.9	1
2014	4	Apple	G	BBB	G_Tier_2	50	64.99	2
2014	4	Apple	G	BBB	G_Tier_3	65	999999	3
2014	4	Apple	G	CCC	G_Tier_1	10	25	2
2014	4	Apple	G	CCC	G_Tier_2	26	50	3

Desired output:

C_Year	C_Qtr	Product	Customer Number	Member Type	Quantity (Sum of Quarter)	Discount_Type	Tier	Discount%
2014	4	Apple	10001	AAA	80	G	G_Tier_3	3
2014	4	Orange	10001	AAA	150	G	G_Tier_3	3
2014	4	Banana	10001	AAA	110	G	G_Tier_3	3
2014	4	Apple	10002	BBB	120	G	G_Tier_3	3
2014	4	Orange	10002	BBB	88	G	G_Tier_3	3
2014	4	Banana	10002	BBB	126	G	G_Tier_3	3
2014	4	Apple	10003	CCC	98	G	G_Tier_3	3

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)

[sujeetsingh]

A best way to explain complex things

[#6]

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).

[hic]

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

[#8]

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

[hic]

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

[#10]

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

[hic]

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.