Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create an auxiliary table from two other tables with an "IntervalMatch" serving as connecting rule. But once I join those two tables I cannot dump its contents inside a third table (since I cannot use two "Resident" tables), so I tried to load the other fields from the "Resident" table in the "Inner Join IntervalMatch" but it generates a cartesian product.
Which would be the best way to generate this third table keeping the interval match logic?
// Loading columns from "SalesOrderHeader"
SpecialTemp1:
Load
SalesOrderID as Special.SaleID,
[OrderDate] as Special.OrderDate,
[TerritoryID] as Special.TerritoryID1,
[OnlineOrderFlag] as Special.Online
Resident [SalesOrderHeader];
// Loading columns from "SalesTerritoryHistory"
SpecialTemp2:
Load
[TerritoryID] as Special.TerritoryID,
[BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,
[SalesTerritoryHistory.StartDate] as Special.StartDate,
if(
len(trim(date([SalesTerritoryHistory.EndDate])))=0,
date('30/06/2009'), // Today()
date([SalesTerritoryHistory.EndDate])
) as Special.EndDate
Resident [SalesTerritoryHistory];
// Joining when "Special.OrderDate" is between "Special.StartDate" and "Special.EndDate"
Inner Join IntervalMatch ( Special.OrderDate )
Load
Special.StartDate,
Special.EndDate
Resident SpecialTemp2;
// Joining with "Employee"
Join
Load
[BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,
[Employee.LastName],
[Employee.FirstName],
[Employee.MiddleName]
Resident [Employee];
// Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"
Left Join
Load
[Special.Online],
[Special.OrderDate],
[Special.SaleID]
Resident SpecialTemp1;
// This attempt requires all the above fields in a single table
SpecialMetrics:
Load
Special.SaleID,
Special.OrderDate,
Special.TerritoryID,
Special.Online,
if(Special.Online='0', Num(286), Special.SalespersonID) as Employee.Code,
Special.StartDate,
Special.EndDate,
[Employee.LastName],
[Employee.FirstName],
[Employee.MiddleName]
Resident SpecialTemp2;
// Dropping temp tables
Drop Tables SpecialTemp1, SpecialTemp2;
Bernardo,
I guess if you just make a simple intervalmatch, it will match histories with no relation. Considering this I think you should use intervalmatch(date ,key)
your script will be this way:
SpecialTemp1:
Load
SalesOrderID as Special.SaleID,
[OrderDate] as Special.OrderDate,
[TerritoryID] as Special.TerritoryID1,
[OnlineOrderFlag] as Special.Online
Resident [SalesOrderHeader];
// Loading columns from "SalesTerritoryHistory"
SpecialTemp2:
Load
[TerritoryID] as Special.TerritoryID,
[BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,
[SalesTerritoryHistory.StartDate] as Special.StartDate,
if(
len(trim(date([SalesTerritoryHistory.EndDate])))=0,
date('30/06/2009'), // Today()
date([SalesTerritoryHistory.EndDate])
) as Special.EndDate
Resident [SalesTerritoryHistory]
Where Exists (Special.TerritoryID1, "TerritoryID");
//this will filter territories that are not in the header
// Joining when "Special.OrderDate" is between "Special.StartDate" and "Special.EndDate"
Inner Join IntervalMatch ( Special.OrderDate, Special.TerritoryID1)
Load
Special.TerritoryID as Special.TerritoryID1
Special.StartDate,
Special.EndDate
Resident SpecialTemp2;
//territoryID as Special.TerritoryID1 will be the key of the intervalmatch
// Joining with "Employee"
Join
Load
[BusinessEntityID-EmployeeBusinessEntityID] as Special.SalespersonID,
[Employee.LastName],
[Employee.FirstName],
[Employee.MiddleName]
Resident [Employee];
// Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"
Left Join (SpecialTemp2)
Load
[Special.Online],
[Special.OrderDate],
[Special.SaleID]
Resident SpecialTemp1
//Where Exists (Special.TerritoryID1, "Special.TerritoryID") //commented
;;
// This attempt requires all the above fields in a single table
SpecialMetrics:
Load
Special.SaleID,
Special.OrderDate,
Special.TerritoryID,
Special.Online,
if(Special.Online='0', Num(286), Special.SalespersonID) as Employee.Code,
Special.StartDate,
Special.EndDate,
[Employee.LastName],
[Employee.FirstName],
[Employee.MiddleName]
Resident SpecialTemp2;
// Dropping temp tables
Drop Tables SpecialTemp1, SpecialTemp2;
Thank you very much Rodolfo.
Unfortunately this solution generates a cartesian product where a sale has all the territories.
You are true, I forgot the key in the second left join,
in this section:
// Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"
Left Join (SpecialTemp2)
Load
[Special.Online],
[Special.OrderDate],
[Special.SaleID],
Special.TerritoryID1
Resident SpecialTemp1
//Where Exists (Special.TerritoryID1, "Special.TerritoryID") //commented
;;
I hope I helped you, this time.
RS
Again, thank you for answering, unfortunately it seems that this join undid the Inner Join IntervalMatch. 😕