topic Re: Interval Match to Multiple Tables in QlikView

Interval Match to Multiple Tables

k_burata — Thu, 11 Aug 2016 01:57:57 GMT

Hi All,

Been struggling with this for a while. Below is a sample data model that I have:

question is: How do I do IntervalMatch the PositionHistory, Department, and Org Tables to a Calendar so that when I select a certain Month-Year it will show all the valid employees within the selcted date.

Cheers.

Re: Interval Match to Multiple Tables

Gysbert_Wassenaar — Thu, 11 Aug 2016 09:14:58 GMT

I don't see why you need to match on multiple tables. Only PositionHistory makes sense to me. Something like this perhaps:

Calendar:

LOAD

Date(MinDate + IterNo()) as Date

WHILE

MinDate + IterNo() <= MaxDate;

LOAD

Date(min(PosStart)) as MinDate,

Date(max(PosEnd)) as MaxDate

RESIDENT

PositionHistory;

IntervalMatchTable:

IntervalMatch(Date)

LOAD

PosStart,

PosEnd

RESIDENT

PositionHistory;

Re: Interval Match to Multiple Tables

k_burata — Fri, 12 Aug 2016 01:07:55 GMT

Hi Gysbert,

Thanks for the reply. An employee might be moved to a different department so I have to trace what department he is in at a certain date.

Is it better if I include the department on the PositionHistory Table by adding a Dept field? But I have to match again the PosStart/PosEnd with DepStart/DepEnd.

Re: Interval Match to Multiple Tables

Gysbert_Wassenaar — Fri, 12 Aug 2016 07:56:18 GMT

The Dep field is part of the PositionHistory table. I assume (and hope for you) that if a person changes from department then a new record is added to the PositionHistory table with a new PosStart value and the old record is updated with the PosEnd value. Destructively updating the Dep field will make it impossible to track changes of department. So the information you want should already be stored in the PositionHistory table.

Re: Interval Match to Multiple Tables

k_burata — Fri, 12 Aug 2016 08:05:24 GMT

Makes sense. Cheers mate.