Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
IntervalMatch and Slowly Changing Dimensions
HIC
Hello Henric,
Thanks so much for your reply! Finally I got the result using Extended IntervalMatch with an inner join
Kiru