IntervalMatch() : Position Assignment Dates

[erickd1190]

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.

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;