topic Re: Multiple IntervalMatches with common dimension and time line in QlikView

Multiple IntervalMatches with common dimension and time line

qliknerd — Tue, 22 Dec 2015 11:30:03 GMT

Hi all

I am in the middle of bringing some of our Workforce data together and have hit a bit of a wall when trying to see things from a point in time. I have digested most of hic's blog info relating to Interval Match and Slowly Changing Dimensions but I'm still not getting the results I expect. Hopefully someone can help me with the wood and trees 😉

Essentially I have three tables WorkerHistory, SicknessAbsence and TDA. All tables have an Employee ID, From and To Dates. I want to merge these intervals like the partitioning example and link them to a calendar. When I select Jun 2015, I am expecting to see all staff, sickness and TDAs for the month I've selected. However I seem to be getting some strange outputs. e.g.

For Employee 1, June 2015:

They have a worker history of 01/02/2015 to 14/12/2015 = Expected

Absence of 05/05/2015 to 08/05/2015 = NOT EXPECTED

No TDA = Expected

I have a feeling it's something to do with how I've linked the %Date field (which I will use for my master calendar link) or it is something relating to the partition in that I could legitimately have an absence with the same start and end date e.g. 13/05/2015 to 13/05/2015

Re: Multiple IntervalMatches with common dimension and time line

Tue, 22 Dec 2015 17:44:39 GMT

Hi Ian,

Is this help ?

Regards

Gilles

Re: Multiple IntervalMatches with common dimension and time line

Tue, 22 Dec 2015 18:13:51 GMT

Trying to get something closer to your request

Re: Multiple IntervalMatches with common dimension and time line

Anonymous — Tue, 22 Dec 2015 18:22:33 GMT

When I have done similar I have first created a single Fact table then done a single IntervalMatch() on it. This gives a far better Data Model than having multiple fact tables and doing an IntervalMatch() on each one.

Re: Multiple IntervalMatches with common dimension and time line

qliknerd — Wed, 23 Dec 2015 13:45:15 GMT

Thanks for looking at Gilles, appreciated. It's not quiet right I'm afraid. There are many more fields in the other that can be used as dimensions, e.g. Department from the Worker History table. Whenever something is selected one table, then all other values are removed.

Worker History is the main file, the other tables contain short term intervals that fall in between which is why I was going down the interval match/partitioning route.

I have reworked the file (removing the TDA table and adding a time element to the Absence Start and Ends) to see if I can get the absences falling at the right periods but as soon as I drop in a calendar/event table the results revert back.

Re: Multiple IntervalMatches with common dimension and time line

qliknerd — Wed, 23 Dec 2015 13:51:49 GMT

Thanks for your suggestion Bill, I may have to look at this as an option.

The issue I have is that the Worker history table contains the main slowly changing dimension details e.g. in order to be able to work out someone's FTE at a point in time. This may change while a person is absent, and the absence data does not contain the FTE.

Unless there is some easy way to generate the missing data for those mid-interval data items?

Re: Multiple IntervalMatches with common dimension and time line

Wed, 23 Dec 2015 16:39:58 GMT

That is why you still have your initial Data in the Data model it is just a link table based on the interval Date.

Regards

Gilles

De : Ian Tsang

Envoyé : mercredi 23 décembre 2015 16:59

À : Courtin, Gilles

Objet : Re: - Multiple IntervalMatches with common dimension and time line

Re: Multiple IntervalMatches with common dimension and time line

qliknerd — Wed, 23 Dec 2015 17:56:57 GMT

Thanks again Gilles. Yes the links to the other tables remain, however if I pick Stress as an absence type from the link table, I still don't know what the FTE (or e.g. Department) of the worker is as the absence interval data is a just subset of the worker history.

%Employee Number	Date_Start	Date_End	Reason	Source	# FTE
1	27/05/2013	15/06/2013	Stress	Absence	NO VALUE

What I need to see is for the absence period (27/05 to 15/06), what is the FTE of the worker during the same interval:

Worker History: 07/04/2012-------------------------------------------------10/12/2014 FTE = 1.0

Absence: -----------------------------27/05/2014----15/06/2014------------------------ FTE = 1.0

However, we will need to see what happens when absences span a change in worker history, e.g.

Worker History: 07/04/2012------------------------10/12/2014 FTE = 1.0

Worker History: 11/12/2014------------------------14/12/2015 FTE = 0.5

Absence: 08/12/2014------15/12/2015 FTE = 1.0 (moving down to 0.5 on 11/12/2014)

Is it possible to achieve in your model? I think I have been looking at this too long and it's almost Christmas!

Regards

Ian

Re: Multiple IntervalMatches with common dimension and time line

Thu, 24 Dec 2015 11:42:15 GMT

Hi Ian,

Just for the Fun a new qvw to play with. I am not sure it corresponds to your business rules that I don't know. For example regarding Absence Period and TDA Period,. I have supposed that they can't be overlapped for an employee and if yes, Absence Period will be over.

Sure you can add department in the Data Model depending on association link you will have to find the best place for it. If it is linked to period so you can add it like FTE if it is to an employee you should put it in an employee table.

Wish you a merry Christmas and an happy new year.

Regards

Gilles

Re: Multiple IntervalMatches with common dimension and time line

qliknerd — Thu, 24 Dec 2015 15:24:00 GMT

Hi Gilles, looks like it could be a solution. I will have a proper look after Christmas so I can get me head fully around it.

A very merry Christmas and an happy New Year to you also.

Thanks

Ian