topic Re: Slowly Changing Dimension Question ( IntervalMatch ) in QlikView

Slowly Changing Dimension Question ( IntervalMatch )

Fri, 20 Nov 2020 18:02:30 GMT

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;

Re: Slowly Changing Dimension Question ( IntervalMatch )

Colin-Albert — Mon, 09 Mar 2015 13:45:53 GMT

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

] ;

Re: Slowly Changing Dimension Question ( IntervalMatch )

Colin-Albert — Mon, 09 Mar 2015 13:49:53 GMT

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

Re: Slowly Changing Dimension Question ( IntervalMatch )

Mon, 09 Mar 2015 13:51:52 GMT

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

Re: Slowly Changing Dimension Question ( IntervalMatch )

umerikhlas — Thu, 02 Jul 2015 08:17:04 GMT

Hi there,

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