Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
