Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Slowly Changing Dimension Question ( IntervalMatch )

Hello, i am trying to implement SCD type 2 in my qvw,

I am trying to show a Territory change for empid = 5,

but this doesnt clearly work because the result is that i get the change, but without the EmployeeName on Territory : North

How can i change this ??

Employees:

LOAD * INLINE [

    Employee, EmpID,SalesTerritory

    Jan, 2, South

    Piet, 3, North

    Klaas, 1, West

    Johan, 8, East

    Harry, 5, East

];

Time:

LOAD * INLINE [

    EffDate,EmpID,SalesTerritory

    1-04-2014,5 ,East

    20-05-2014,5,North

  

 

  

];

inner join (Time)

IntervalMatch (EffDate,SalesTerritory)

Load distinct EffDate

  

Resident Time;

1 Solution

Accepted Solutions
Colin-Albert

There is insufficient data in your example to apply an interval match.

You have an effective date and employee ID and sales territory in the Time table, but your load needs to convert the Eff Date to a start date & end date, so a few more values would help you to test this.

Then you would need other data e.g sales, that has transactions between these start and end dates.

The interval match will then link a date in the sales table to the relevant data range in the Territory Table.

E.g. in the data below, Interval match can be used to see which employee was responsible for the East Territory on 05-04-2014

e.g.

Territory:

LOAD * INLINE [

    StartDate, EndDate, EmpID, SalesTerritory

    1-04-2014, 19-05-2014,5 ,East

    20-05-2014, 11-03-2015,5,North

      1-04-2014, 19-07-2014,3 ,West

     20-07-2014, 19-07-2014,2 ,West

] ;

Sales

LOAD * INLINE [

Date, Sales Territory, Value

5-04-2014, East, 1000

] ;

View solution in original post

4 Replies
Colin-Albert

There is insufficient data in your example to apply an interval match.

You have an effective date and employee ID and sales territory in the Time table, but your load needs to convert the Eff Date to a start date & end date, so a few more values would help you to test this.

Then you would need other data e.g sales, that has transactions between these start and end dates.

The interval match will then link a date in the sales table to the relevant data range in the Territory Table.

E.g. in the data below, Interval match can be used to see which employee was responsible for the East Territory on 05-04-2014

e.g.

Territory:

LOAD * INLINE [

    StartDate, EndDate, EmpID, SalesTerritory

    1-04-2014, 19-05-2014,5 ,East

    20-05-2014, 11-03-2015,5,North

      1-04-2014, 19-07-2014,3 ,West

     20-07-2014, 19-07-2014,2 ,West

] ;

Sales

LOAD * INLINE [

Date, Sales Territory, Value

5-04-2014, East, 1000

] ;

Colin-Albert

Have a look at this post for an explanation of how to use Interval Match

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Slowly Changing Dimensions & Interval Match

http://community.qlik.com/docs/DOC-4310

Not applicable
Author

Thanks Colin for Reply, i have done exatcly as u say, and how does the intervalmatch function look like for the above code?

umerikhlas
Contributor III
Contributor III

Hi there,

I am facing the almost similar situation. Anakin00 can you please tell us, how you resolved your query?