Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
3ernardo
Contributor III
Contributor III

Generating a table from an intervalmatch join

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;

13 Replies
rodolfoag
Contributor III
Contributor III

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;

3ernardo
Contributor III
Contributor III
Author

Thank you very much Rodolfo.

Unfortunately this solution generates a cartesian product where a sale has all the territories.

rodolfoag
Contributor III
Contributor III

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

3ernardo
Contributor III
Contributor III
Author

Again, thank you for answering, unfortunately it seems that this join undid the Inner Join IntervalMatch.    😕