IntervalMatch() : Position Assignment Dates

erickd1190 — Tue, 15 Aug 2017 19:38:43 GMT

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;

topic IntervalMatch() : Position Assignment Dates in App Development

IntervalMatch() : Position Assignment Dates