1 Reply Latest reply: Oct 4, 2012 2:07 PM by Scott Arnold RSS

    Creating a "link" table using an IntervalMatch

      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:

       

      QVDataModel.JPG

       

      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

          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;