How join can be used with intervalmatch()

[stanlyrj]

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

[thomaslg_wq · Accepted solution]

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;

[stanlyrj]

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