Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
] ;
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
] ;
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
Thanks Colin for Reply, i have done exatcly as u say, and how does the intervalmatch function look like for the above code?
Hi there,
I am facing the almost similar situation. Anakin00 can you please tell us, how you resolved your query?