topic Re: Regarding IntervalMatch problem: in QlikView

Regarding IntervalMatch problem:

Tue, 24 Jun 2014 14:21:21 GMT

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

Re: Regarding IntervalMatch problem:

hic — Tue, 24 Jun 2014 21:09:20 GMT

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

Re: Regarding IntervalMatch problem:

Tue, 24 Jun 2014 22:36:20 GMT

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

Re: Regarding IntervalMatch problem:

hic — Wed, 25 Jun 2014 06:25:54 GMT

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

Slowly Changing Dimensions

IntervalMatch and Slowly Changing Dimensions

HIC

Re: Regarding IntervalMatch problem:

Wed, 25 Jun 2014 20:52:35 GMT

Hello Henric,

Thanks so much for your reply! Finally I got the result using Extended IntervalMatch with an inner join

Kiru