topic Re: How join can be used with intervalmatch() in QlikView

How join can be used with intervalmatch()

stanlyrj — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm new to Qlik View. I'm working on an exercise and I'm facing a problem which I'm not able to find a solution.

Here I have two tables Facts and Sales_Territory. (Shown as in the below Image)

I want to join the field EmployeeID(from table SalesTerritory) to the table Facts(As shown in the below Image).

I have used the following script for this.

Facts:

Load * From

[lib://QVD\Tra\Facts\SalesOrder.qvd](qvd);

SalesTerritory:

Load *

From [lib://QVD\Tra\Dims\SalesTerritory.qvd](qvd);

Join

IntervalMatch(OrderDate,TerritoryID)

Load StartDate,EndDate,TerritoryID Resident SalesTerritory;

left join(Facts)

Load

TerritoryID,

EmployeeID

Resident SalesTerritory;

But this does not give the desired output. Can anyone help me, please...........

Thank You

Stanly R Johns

Re: How join can be used with intervalmatch()

thomaslg_wq — Thu, 22 Dec 2016 17:47:11 GMT

Hi,

hehe it's a little bit tricky.

This script will do it, you just have to understand it now. Tell me if you miss something:

Territories:

LOAD

Territoy_ID&'/'&StartDate&'/'&EndDate as Territory_From_To,

Territoy_ID,

Employee_ID,

StartDate,

EndDate

FROM [lib://desktop/Classeur1.xlsx]

(ooxml, embedded labels, table is Territory);

Sales:

LOAD

Territoy_ID&'/'&"Order Date" as Territory_Date,

SalesOrderID,

Territoy_ID as Territoy_ID_Temp,

"Order Date",

Amount

FROM [lib://desktop/Classeur1.xlsx]

(ooxml, embedded labels, table is Fact);

Temp:

Intervalmatch([Order Date],Territoy_ID_Temp)

LOAD

StartDate,

EndDate,

Territoy_ID as Territoy_ID_Temp

resident Territories;

outer join(Territories)

LOAD

Territoy_ID_Temp&'/'&[Order Date] as Territory_Date,

Territoy_ID_Temp&'/'&StartDate&'/'&EndDate as Territory_From_To

resident Temp;

Drop table Temp;

Re: How join can be used with intervalmatch()

stanlyrj — Thu, 22 Dec 2016 19:31:40 GMT

Hi Thomas,

Thank you so much for your reply.

Your script worked perfectly and I got the solution for the problem. This is my very first post on Qlik Community and I got the correct answer in the first reply itself. I'm so happy.

Thanks again!

Best Regards,

Stan