Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am new to QlikView and I was reading about Interval Match extended.
Can anybody tell me when would you use Interval Match (extended) version over the normal Interval Match?
They don't look too different to each other so I would like to know when to use each one!
I have already read the Help and Reference Manuel but I still don't understand. Can anybody please simplify with an example?
The extended version is used when you need an additional key field to distinguish between different ranges that match your numeric value.
This is intervalmatch...
Hair_Disaster:
LOAD * INLINE [
Min_Percentage, max_Percentage, Kerotine_Value
40, 60, Low
61, 70, Medium
71, 80, Normal
81, 90, High
92, 100, Above All
-10, 0, Negative
];
Hair_Shine:
LOAD * INLINE [
Percentage, Name
45, Anand
55, Karim
65, Islam
70, Ancy
92, Priya
110, Manish
500, Tarjani
];
Left Join
IntervalMatch(Percentage) Load
Min_Percentage, max_Percentage
Resident Hair_Disaster;
Left Join (Hair_Shine)
Load * Resident Hair_Disaster;
Drop Table Hair_Disaster;
=====================================
This is extended intervalmatch example..
Orders:
LOAD
Date(Date#(OrderDate,'DD-MM-YYYY')) as OrderDate,
Item,
Quantity
INLINE
[
OrderDate, Item, Quantity
01-01-2013, X0001, 12
31-12-2013, X0001, 12
01-02-2014, X0001, 15
];
Temp:
LOAD
Date(Date#(StartDate,'DD-MM-YYYY')) as StartDate,
Date(Date#(EndDate,'DD-MM-YYYY')) as EndDate,
Item,
Price
INLINE
[
StartDate, EndDate, Item, Price
01-01-2013, 31-12-2013, X0001, 3.45
01-01-2014, 31-12-2014, X0001, 3.55
];
Inner Join
IntervalMatch(OrderDate, Item)
LOAD DISTINCT StartDate, EndDate, Item Resident Temp;
Left Join(Orders)
LOAD * Resident Temp;
Drop Table Temp;
=================================
Swuehl has already explained the difference..
Well, maybe not enough.
Don't know what to add to the examples that are contained in the help (there are examples for both versions when looking at the IntervalMatch() help page):
Hi all,
Thank you for the replies. I am still having trouble understanding why I need additional key field to distinguish between different ranges that match your numeric value.
Why would this be the case? Sorry it's not making sense to me yet!
Have you looked into the example I linked into the HELP pages?
This example demonstrates why you would need a key for example if you have multiple production lines with events and time intervals you need to reference to specific lines.
A simple case would be when you try to match different intervals for specific departments to an employee that has a timestamp and works in a specific department at that moment. Department would be an additional "dimension"-like value that should be taken into account when trying to match timestamps to intervals.
The explanation for IntervalMatch (Extended Syntax) in QV Help is really good, but the example is a bit thin.
Peter
Hi Swuehl yes your earlier linked helped as well as Manish's example. I am having more difficulty simplifying why key field has to be used to distinguish different ranges. What would happen if this wasn't the case?
Sorry if it sounds like a silly question, I am trying to really understand the importance of the key field
You would match a numerical value to all intervals, regardless of the keyfield value that should match 1:1. You get an overly complex synthetic key and too many matches.
The most common application is to match a slowly changing dimension (SCD) which contains start and end dates, a value, and the item to which this dimension value applies.
As an example, consider a set of sales people that are grouped into geographical areas, which might change from time to time, and we want to report the sales by area with the sales assigned to the correct area based on the SCD. The Sales table contains a transaction date, salesperson ID, sales amount (among others). The SCD in the Intervals table would contain: sales person ID, start date, end date and area code. so we would use:
Left Join (Sales)
IntervalMatch(SalesDate, SalesPersonID) // these are the fields from Sales
LOAD StartDate, EndDate, SalesPersonID // these are from Intervals
Resident Intervals;
Now the sales area (in Intervals) will be correctly associated* with the sales transactions (in Sales)according to the area to which the sales person belonged in the sales date range between StartDate and EndDate.
*via the composite key StartDate, EndDate, SalesPerson between the Sales table and the Intervals table