Working of Interval MAtch

[manojkulkarni]

Hi All,

Would like to know how interval match works internally ?

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

[anbu1984]

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; 

[datanibbler]

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