Regarding IntervalMatch problem:

[#1]

Hello Guys,

Your helps are really appreciated. I kind of stuck with the below interval match problem. I actually have 4 tables

SalesOrderHeader:

SQL select

    OrderDate,

       SalesOrderID,

      TerritoryID

FROM SalesOrderHeader;

SalesOrderDetail:

SQL SELECT

    SalesOrderID,

       UnitPrice,

    UnitPriceDiscount

FROM SalesOrderDetail;

Employee:

SQL SELECT

    EmployeeBusinessEntityID,

    EndDate as Employee_EndDate,

    FirstName,

       LastName,

       ParentEmployeeBusinessEntityID,

     StartDate as Employee_StartDate

   FROM Employee;

Transaction:

Load *,1

resident SalesOrderHeader;

concatenate

load *

resident SalesOrderDetail;

drop table SalesOrderHeader,SalesOrderDetail;

SalesTerritoryHistory:

SQL SELECT BusinessEntityID as EmployeeBusinessEntityID,

    EndDate,

    StartDate,

    TerritoryID

FROM SalesTerritoryHistory;

join(SalesTerritoryHistory)

IntervalMatch(OrderDate)

load Distinct StartDate,EndDate resident SalesTerritoryHistory;

join(SalesTerritoryHistory)

load * resident Transaction;

drop table Transaction;

The problem is that I am not getting any firstname and lastname from Employee table to the corresponding sales period in the straigt  table.

Kindly let me know if any one finds any bug with the above script

Thanks,

Kiru

[hic]

The problem is that you concatenate the SalesOrderHeader and SalesOrderDetail. They should remain as two tables.

Further, all your joins are really unnecessary. Try the following instead:

SalesOrderHeader:

SQL SELECT OrderDate, SalesOrderID, TerritoryID FROM SalesOrderHeader;

SalesOrderDetail:

SQL SELECT SalesOrderID, UnitPrice, UnitPriceDiscount FROM SalesOrderDetail;

Employee:

SQL SELECT EmployeeBusinessEntityID, EndDate as Employee_EndDate,

    FirstName, LastName, ParentEmployeeBusinessEntityID, StartDate as Employee_StartDate

   FROM Employee;

SalesTerritoryHistory:

SQL SELECT BusinessEntityID as EmployeeBusinessEntityID,

    EndDate, StartDate, TerritoryID FROM SalesTerritoryHistory;

IntervalMatch:

IntervalMatch(OrderDate)

LOAD distinct StartDate, EndDate RESIDENT SalesTerritoryHistory;

HIC

[#3]

Hello Henric,

I am glad to have your reply ;Thank you so much!

I tried your code but I am getting a circular reference and I have attached it for your reference.

And also, I am not getting all the salespersons name  while calculating the corresponding sales of that person in a chart after adding the interval match function in the application !

Thanks Again!

Best,

Kiru

[hic]

Please post some data (Use advanced editor).

HIC

By the way - if you want the Date and the TerritoryID to link simultaneously, you need to treat this as a Slowly Changing Dimension. See more on

Slowly Changing Dimensions

IntervalMatch and Slowly Changing Dimensions

HIC

[#5]

Hello Henric,

Thanks so much for your reply! Finally I got the result using Extended IntervalMatch with an inner join

Kiru