Intervalmatch help ?

[#1]

HI all,

I have a requirement to work wih a changing dimension that handles history and the help suggests the use of intervalMatch however ive knocked up the below example but it doesnt give me the results im after can anyone suggest the correct way to do this ?

Sales occuring After 3 oct should be allocated as from Warehouse 2 without affecting existing Sales Values from Warehouse 1

[Code]
DimProducts:
LOAD * INLINE [ProductCode, Warehouse,Start,End
361087/21/510, WareHouse1,1 oct 2009, 2 oct 2009
361087/21/510, Warehouse2,3 oct 2009, 4 oct 2009];

Sales:
LOAD * INLINE [ProductCode, SaleID,SalesDate
// 361087/21/510, 1, 1 oct 2009
// 361087/21/510, 2, 2 oct 2009
// 361087/21/510, 3, 3 oct 2009];

IntervalMatch (SalesDate) load Start,End resident DimProducts;

//expected result
//SaleId, Warehouse
//1,Warehouse1
//2,Warehouse1
//3,Warehouse2
[\Code]

[#2]

I think you have to inner join it back to itself starting with the dates and including any other info from your original table that you want to keep with it. When I used intervalmatch I then loaded it to a table with just the date (without the start and end) and dropped the temporary table since I had the start & end dates elsewhere and did not want them in this table. Hope this helps.

<h4>SalesProductTemp: </h4><h4>IntervalMatch (SalesDate) </h4><h4>LOAD DISTINCT Start,End resident DimProducts;</h4><h4>Inner Join </h4><h4>LOAD DISTINCT </h4><h4>Start, End, ProductCode, Warehouse Resident DimProducts;</h4>
 <h4>SalesProduct: </h4><h4>LOAD DISTINCT ProductCode,Warehouse,Date Resident SalesProductTemp;</h4><h4>Drop Table SalesProductTemp;