topic Re: Working of Interval MAtch in QlikView

Working of Interval MAtch

manojkulkarni — Wed, 13 May 2015 12:27:44 GMT

Hi All,

Would like to know how interval match works internally ?

Will it consider >= & <= while doing match of date fields..

Re: Working of Interval MAtch

anbu1984 — Wed, 13 May 2015 13:02:34 GMT

Yes it will consider >= and <= while doing match of date fields. Check this example

Orders:
LOAD 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#(StartDate,'DD-MM-YYYY') As StartDate, 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;
Join(Orders)
LOAD * Resident Temp;

Re: Working of Interval MAtch

datanibbler — Wed, 13 May 2015 13:06:41 GMT

Hi Manoj,

the IntervalMatch() is actually one of two necessary steps.

Say you have two tables. One has every day's date while the other just has a start_date and an end_date (for some fact)

=> You cannot link the tables as they are because the grain of the tables is different

=> Using IntervalMatch(), you add the corresponding interval to every single day's date in your first table.

=> Now you have a field that you can join on to get that fact into your fine-grained table.

HTH