topic Problem with IntervalMatch and Slowly changing Dimension in QlikView

Problem with IntervalMatch and Slowly changing Dimension

Tue, 22 Sep 2015 11:58:10 GMT

Hello,

I browsed materials on community, including HIC posts, but I am still not able to do what I want...

I've got a table which shows how employee allocation was changing:

Employee ID	Division	Start	End
A	A1	06-Jan-2015	19-Jul-2015
A	B1	20-Jul-2015	20-Sep-2015
A	V2	21-Sep-2015	01-Oct-2017

And I want to get a table like this:

Employee ID	MonthYear	FromDate	ToDate	Division
A	6-2015	01-Jun-2015	30-Jun-2015	A1
A	7-2015	01-Jul-2015	31-Jul-2015	B1
A	8-2015	01-Aug-2015	31-Aug-2015	B1
A	9-2015	01-Sep-2015	30-Sep-2015	V2
A	10-2015	01-Oct-2015	31-Oct-2015	V2
A	11-2015	01-Nov-2015	30-Nov-2015	V2
A	12-2015	01-Dec-2015	31-Dec-2015	V2

Basically I want to see in which Division employee was working in every month of a year (Last day of a month is indicator). Table will be generated from master calendar, but for this example we can just hardcode 7 months...

Example:

For 9-2015 employee was in V2 division, because it started to be active end of Sep.

How to approach this? I am pretty sure IntervalMatch is the answer, but I have no luck with implementing it properly.

BR,

Kuba

Re: Problem with IntervalMatch and Slowly changing Dimension

Wed, 23 Sep 2015 08:23:08 GMT

Hello,

Sorry for bumping it, unfortunately I am still not able to find a proper solution.

BR,

Kuba

Re: Problem with IntervalMatch and Slowly changing Dimension

thimma_paul — Wed, 23 Sep 2015 08:58:39 GMT

Hi,

Div column is denoting Jan to June as A1, July to Aug end as B1, and Sep to Dec as V2 rite...

BR,

Thimm

Re: Problem with IntervalMatch and Slowly changing Dimension

Wed, 23 Sep 2015 09:17:59 GMT

Hello,

Not really. As you can see in example B1 starts on 20-Jul-2015, I mentioned that I must derive what Div was there in a particualr month, but looking at last day in that month.

In last day of month July B1 was active (as it started 20-Jul).

This is also why in Sep V2 is shown (as it started 21-Sep and was valid in last day of Sep)