0 Replies Latest reply: Aug 15, 2017 3:38 PM by Erick Dameron RSS

    IntervalMatch() : Position Assignment Dates

    Erick Dameron

      Hello!

       

      I am having trouble getting my interval match to work correctly, or maybe it is and i'm misunderstanding its use.

      My goal is that when my end user filters on a date or dates, my application will calculate the targets for the assigned position (per staff) within that timeframe.

      Example: I am working as a electrician from 10/01//2016 to 12/31/2016 and my target during that time is 1000.

      From 01/01/2017 to 03/31/2017 I am working as a lead electrician and my target is 1500.

      Therefore when my end user selects 10/01/2016 to 03/31/2017 they see my target is 2500 total.

       

      Right now I get a line or record for every start and end date for every position, when there should only be multiples if that employee has moved positions.

       

      Any thoughts?

       

       

      I have a a data structure like this.

       

      structure.PNG

       

      and a load script as such

       

      LOAD *,

      transfer_date-visitdate AS Notedays,

      IF(transfer_date - visitdate<4,1,0) AS Compliant;

       

       

      History:

      LOAD

          visitdate,

          visitid,

          client_id,

          service,

          staffid,

          cpt_code,

          program,

          transfer_date,

          "rate",

          emp_status

      FROM [lib://It- Drive Reporting (tbh_erickd)/QVD Files\History.QVD]

      (qvd) Where service <> 'Flex Funds';

       

      Load *,

      IF(EmployeeStatus = 'A',1,0) AS EmployeeStatusFlag;

       

      EmpIncentivelink:

      LOAD

          "Employee ID" AS staffid,

          "Last Name First",

          EmployeeStatus,

          "Last Hire Date",

          Today()-[Last Hire Date] AS Tenure,

          Site,

          Program,

          "Position Description",

          "Program-Position Description",

          "Supervisor Name",

          UPPER([Supervisor Name]) As _Supervisor,

          CorporateLevelCode,

          "Termination Date",

          TargetKey

      FROM [lib://It- Drive Reporting (tbh_erickd)/Qlik\Emp-Incentive-Link.xlsx]

      (ooxml, embedded labels, table is [Emp-Incentive-Link]);

      Join

      LOAD

          Position,

          TargetKey,

          MonthlyPT,

          DailyPT

      FROM [lib://It- Drive Reporting (tbh_erickd)/Target Table.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      LIB CONNECT TO 'Microsoft_SQL_Server_tbh-warehouse (tbh_erickd)';

       

      LOAD Empno,

      FTE,

      PositionNo,

      PrimaryPosition,

      Rank,

      Date(StartDate) AS StartDate,

      Date(StopDate) As StopDate,

      LastUpdate;

       

      Intervals:

      SELECT Empno,

      FTE,

      PositionNo,

      PrimaryPosition,

      Rank,

      StartDate,

      StopDate,

      LastUpdate

      FROM REPLTAZ.dbo.PositionControlAssignment;

       

       

      BridgeTable:

      IntervalMatch ( visitdate)

      Load distinct

      StartDate,

      StopDate

      resident Intervals;

       

       

      MinMaxDate:

      LOAD Min(visitdate) As MinDate

       

      Resident History;

       

      LET vMinDate = PEEK('MinDate',-1,'MinMaxDate')-1;

      LET vMaxDate = FLOOR(MonthEnd(Today(1)));

       

      Drop Table MinMaxDate;

       

      Calendar:

      Load Dual(fYear-1 &'/'& fYear, fYear) AS FYear,

      Dual(Month, fMonth) AS FMonth,

          DUAL ('Quarter' & Ceil(fMonth/3), Ceil(fMonth/3)) AS FQuarter,

          *;

        

      Load Year + IF(Month>=$(vFM), 1,0) As fYear,

      Mod(Month-$(vFM),12)+1 As fMonth,

          DUAL('Quarter' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,

         *;

      Load visitdate,

      Year(visitdate) As Year,

      Month(visitdate) as Month,

      Week(visitdate) as Week

       

      Resident History;

       

      LOAD Date(recno()+$(vMinDate)) As visitdate AutoGenerate vMaxDate - vMinDate;