Creating a "link" table using an IntervalMatch

Thu, 27 Sep 2012 20:06:37 GMT

I am working on a QV app and have almost got it where I want it but am getting stuck on one final table I want to create in the script, which I believe requires an IntervalMatch. My data model looks like this:

The ContractRoleDemand table is uniquely defined by the ActivityContractRoleKey and contains all the data I need to divide up the required workload into daily (workday) increments. The WorkStart and WorkFinish fields provide the date range desired. The WorkCalendar contains a row for each workday (i.e., excluding weekends – holidays may come later) occurring in the 4-year period starting 1/1/2011 – 12/31/2014.

As pictured above, I want to create a table linking these 2 tables that would contain the ActivityContractRoleKey from ContractRoleDemand and each WorkDate from WorkCalendar that occurs between the WorkStart and WorkFinish dates from ContractRoleDemand.

Any advice you can give me would be appreciated. Also, if the ultimate solution requires and AGGR to pull meaningful daily workload information into a visualization, I’d like to know how to code the AGGR expression, as that function has not yet “clicked” with me.

Re: Creating a "link" table using an IntervalMatch

Thu, 04 Oct 2012 18:07:50 GMT

With the assistance of a consultant, the following solution was devised. This created the "link" table I wanted. The WorkStart and WorkFinish fields were dropped from the original table to avoid generating a synthetic key.

ContractRoleMatch:

LOAD ActivityContractRoleKey,

WorkStart,

WorkFinish

Resident ContractRoleDemand;

Inner Join (ContractRoleMatch)

IntervalMatch(WorkDate)

LOAD WorkStart,

WorkFinish

Resident ContractRoleMatch;

DROP Fields WorkStart, WorkFinish From ContractRoleDemand;

topic Re: Creating a "link" table using an IntervalMatch in QlikView

Creating a "link" table using an IntervalMatch

Re: Creating a "link" table using an IntervalMatch