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
Read about the latest Qlik Community enhancements on the Community News blog!
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
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;


Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
3ernardo
Contributor III
Contributor III
Author

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
3ernardo
Contributor III
Contributor III
Author

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
3ernardo
Contributor III
Contributor III
Author

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
3ernardo
Contributor III
Contributor III
Author

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;

3ernardo
Contributor III
Contributor III
Author

Any idea?