Slowly Changing Dimension Question ( IntervalMatch )

[#1]

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;

[Colin-Albert · Accepted solution]

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

[#4]

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

[umerikhlas]

Hi there,

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