Generating a table from an intervalmatch join

[Anonymous]

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;

[Anil_Babu_Samineni]

May be this?

//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];

Inner Join IntervalMatch (Special.OrderDate)

Load Special.StartDate, Special.EndDate

Resident SpecialTemp2;

// 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;

[Anonymous]

Thank you very much for answering!

Aside from deleting the comment:  "// Failed attempt to join the missing columns from the "SpecialTemp1" to the "SpecialTemp2"" I cant find any difference to my code. I need the columns [Special.Online], [Special.OrderDate] and [Special.SaleID] in the table too.

[Anil_Babu_Samineni]

Diff is here

Left Join (SpecialTemp2)

Load

    [Special.Online],

    [Special.OrderDate],

    [Special.SaleID]

Resident SpecialTemp1;

[Anonymous]

Thanks for clarifying. Unfortunately the problem persists, for each SaleID it has a bunch of TerritoryID. Like if it is not respecting the relation of the "Special.TerritoryID" from [SalesOrderHeader] with the "Special.TerritoryID" from [SalesTerritoryHistory] and it just take all salesperson working in that period regardless of the region.

By the way, there is a typo in the firs load:

[TerritoryID] as Special.TerritoryID1,

is actually:

[TerritoryID] as Special.TerritoryID,

[Anil_Babu_Samineni]

True, It's because field constraints. And you need to work for that using

Left Join (SpecialTemp2)

Load

    [Special.Online],

    [Special.OrderDate],

    [Special.SaleID]

Resident SpecialTemp1 Where Exists (Special.TerritoryID1, Special.TerritoryID);

[Anonymous]

It won't find the expression from the "exists" function.

Field 'Special.TerritoryID' not found

I can't figure out why since this field belongs to the SpecialTemp2 table.

[Anil_Babu_Samineni]

My bad, Can you post whole sctipt in qvs?

[Anonymous]

Sorry, but my last comment is waiting for moderation for the last eight hours due to a link to the code. While the last comment is waiting, here is the script:

// 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 (SpecialTemp2)

Load

    [Special.Online],

    [Special.OrderDate],

    [Special.SaleID]

Resident SpecialTemp1

Where Exists (Special.TerritoryID1, "Special.TerritoryID");;

// 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;

[Anonymous]

Any idea?