Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

k_burata
New Contributor III

Interval Match to Multiple Tables

Hi All,

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

Capture.PNG

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.

1 Solution

Accepted Solutions

Re: Interval Match to Multiple Tables

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.


talk is cheap, supply exceeds demand
4 Replies

Re: Interval Match to Multiple Tables

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;


talk is cheap, supply exceeds demand
k_burata
New Contributor III

Re: Interval Match to Multiple Tables

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

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.


talk is cheap, supply exceeds demand
k_burata
New Contributor III

Re: Interval Match to Multiple Tables

Makes sense. Cheers mate.

Community Browser