Here is the Machines Load Script:
MACHINE_TIME_INPUT:
LOAD JOB_NUMBER as MCLINE_JO_KEY,
MACHINE_CODE&'-'&JOB_NUMBER as MCLINE_JO_KEY_MTI,
// COMPANY_CODE,
/// FACTORY,
// BATCH_NUMBER,
// SEQUENCE,
STATUS_FLAG as MTI_STATUS_FLAG,
MACHINE_CODE as MTI_MACHINE_CODE,
JOB_NUMBER as MTI_JOB_NUMBER,
// PROCESS_STAGE,
// RECIPE_LINE_NUMBER,
START_TIME,
END_TIME,
SETUP_TIME,
RUNTIME,
IF(if(WildMatch(MACHINE_CODE,'*DOWN*'), 'DOWN','UP')='DOWN',RUNTIME, 0) as Machine_Down,
IF(if(WildMatch(MACHINE_CODE,'*DOWN*'), 'DOWN','UP')='UP',RUNTIME,0) as Machine_UP,
TIME_UNIT as MTI_TIME_UNIT,
// COST_TIME_UNIT,
// MACHINE_RECOVERY_FLAG,
// MACHINE_RECOVERY_COST,
OUTPUT_QTY,
SYSTEM_DATE,
PERIOD,
"YEAR",
OPERATOR_ID,
Date(Floor(START_DATE_TIME), 'MM/DD/YYYY') as START_DATE_TIME,
MONTH(START_DATE_TIME) as month_mac,
date(START_DATE_TIME) AS START_DATE_TIME1 ,
END_DATE_TIME,
PM_DATE,
// APS_SETUP_START_DATE,
// APS_SETUP_END_DATE,
// APS_MID_BATCH_DATE_TIME,
TIME_ENTRY_METHOD
// OH_ALLOCATION_COST,
// OH_ALLOCATION_FLAG,
// RUNTIME_COST_ACTUAL,
// SETUP_COST_ACTUAL,
// OVERHEAD_COST_ACTUAL
FROM [lib://QVD L1 (farcnt_cpelton)/MACHINE_TIME_INPUT.qvd]
(qvd)
Where year(START_DATE_TIME)>=year(Today())-3;
MACHINES:
LEFT KEEP(MACHINE_TIME_INPUT)
Here is the Master Calendar Load Script:
/// Preparing Calendar QVD//
tmp_MaxDate:
Load min((START_DATE_TIME)) as mindate,
max((START_DATE_TIME)) as maxdate;
Load Distinct date(START_DATE_TIME,'MM/DD/YYYY') as START_DATE_TIME
FROM [lib://QVD L1 (farcnt_cpelton)/MACHINE_TIME_INPUT.qvd]
(qvd) where Year(date(START_DATE_TIME))>=year(today())-3;
LET vMinDate = Peek('mindate',0,'tmp_MaxDate');
LET vMaxDate = Peek('maxdate',0,'tmp_MaxDate');
LET vToday = $(vMaxDate);
LET vTTM_MinDate= $(vMaxDate)-365;
LET vPTTM_MinDate= $(vMaxDate)-730;
LET vPPTTM_MinDate= $(vMaxDate)-1095;
//LET vAsOfDate = num(Today())-1; delete this variable
drop table tmp_MaxDate;
//Calendar AutoGenerate from Minimum Order date to Maximum START_DATE_TIME So that way we will have all dates even though we don't have transactions//
TEMP_CALENDAR:
LOAD
Date($(vMinDate) + RowNo() - 1) AS START_DATE_TIME
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
////mapping tables
//MappingDOW:
//Mapping LOAD * INLINE [
// Day, Day of Week
// 1, 1
// 2, 2
// 3, 3
// 4, 4
// 5, 5
// 6, 5
// 7, 5
//];
//
//Creation of Master Calendar Using the AutoGenerate Calendar table by doing resident of TEMP_CALENDAR//
MasterCalendar:
Load *,
if(mod(Month,3)=1,1,if(Mod(Month,3)=2,2,3)) as Quarter_Month_Serial_No,
IF(QuarterID=PEEK(QuarterID),PEEK(DayOfQuarter)+1,1) AS DayOfQuarter
;
LOAD
START_DATE_TIME,
START_DATE_TIME aS START_DATE_TIME2,
( Year(START_DATE_TIME)*12) + Month(START_DATE_TIME) as MonthIndex,
Week(START_DATE_TIME) AS Week,
Year(START_DATE_TIME) AS Year,
Month(START_DATE_TIME) AS Month,
'Q' & ceil(Month(START_DATE_TIME)/3) AS Quarter,
ceil(Month(START_DATE_TIME)/3) AS QuarterID,
Day(START_DATE_TIME) AS Day,
Day(START_DATE_TIME) AS DayofMonth,
text(WeekDay(START_DATE_TIME)) AS WeekDay,
Month(START_DATE_TIME)*100+Day(START_DATE_TIME) as DayOfYear,
//ApplyMap('MapWeekDayNo',WeekDay(START_DATE_TIME)) AS WeekdayNo,
Date(monthstart(START_DATE_TIME), 'MMM-YYYY') AS MonthYear,
Year(START_DATE_TIME) & num(Month(START_DATE_TIME), '00') AS YearMonth,
year(Today())&num(Month(Today()),'00') as Today_YearMonth,
WeekYear(START_DATE_TIME) & num(Week(START_DATE_TIME), '00') AS YearWeek,
if(Year(START_DATE_TIME)=WeekYear(START_DATE_TIME), Week(START_DATE_TIME) - Week(MonthStart(START_DATE_TIME)) + 1, Week(START_DATE_TIME) + 52 - Week(MonthStart(START_DATE_TIME)) + 1) AS [Week in Month], //week number within a month, 1, 2, 3, 4, 5, 6
InYearToDate(START_DATE_TIME, $(vToday), 0) * -1 AS CurYTDFlag,
InYearToDate(START_DATE_TIME, $(vToday), -1) * -1 AS LastYTDFlag,
InMonthToDate(START_DATE_TIME, $(vToday), 0) * -1 as CurMTD_flag,
InMonthToDate(START_DATE_TIME, $(vToday), -1) * -1 as LastMTD_flag,
num(Month(START_DATE_TIME)) as MonthNo,
if(START_DATE_TIME<=date($(vMaxDate)),if(START_DATE_TIME>date($(vTTM_MinDate)),1,null()),null()) as TTM_Flag,
if(START_DATE_TIME<=date($(vTTM_MinDate)),if(START_DATE_TIME>date($(vPTTM_MinDate)),1,null()),null()) as PTTM_Flag,
if(START_DATE_TIME<=date($(vPTTM_MinDate)),if(START_DATE_TIME>date($(vPPTTM_MinDate)),1,null()),null()) as PPTTM_Flag
RESIDENT TEMP_CALENDAR
ORDER BY START_DATE_TIME ASC;
DROP TABLE TEMP_CALENDAR;
left join (MasterCalendar)
LOAD WeekDay,
WeekDayNo INLINE [
WeekDay, WeekDayNo
Mon, 1
Tue, 2
Wed, 3
Thu, 4
Fri, 5
Sat, 6
Sun, 7
];
//==================Variable Initialization for UI================
Let _PeriodSelector = 'TTM';
Let _PreviousPeriodSelector = 'PTTM';
MaxDoX:
LOAD Max([DayOfYear]) as DoY
, Max([DayOfQuarter]) as DoQ
, Max([DayofMonth]) as DoM
,max(QuarterID) as QID
// , Max(QuarterIndex) as QtrIndex
, Right(MaxString(Month),3) as myMonth
,max(Quarter_Month_Serial_No) as Quarter_Month_Serial_No1
// , if(Match(Right(MaxString(Month),3), MonthNames) >= 3, 1, 0) as MarchOrLater
Resident MasterCalendar
where START_DATE_TIME = date($(vMaxDate));
LET vFixedDoY = Peek('DoY', 0, 'MaxDoX');
LET vFixedDoQ = Peek('DoQ', 0, 'MaxDoX');
LET vFixedDoM = Peek('DoM', 0, 'MaxDoX');
//LET vFixedCQIndex = Peek('QtrIndex', 0, 'MaxDoX');
LET vFixedMonth = Peek('myMonth', 0, 'MaxDoX');
LET vFixedCQtrIndex = Peek('QID', 0, 'MaxDoX');
Let vQtr_Mth_Serial_No = Peek('Quarter_Month_Serial_No1', 0, 'MaxDoX');
LET vCDoY = vFixedDoY;
LET vCDoQ = vFixedDoQ;
LET vCDoM = vFixedDoM;
LET vCM = vFixedMonth;
LET vPYDoY = vFixedDoY;
LET vPYDoQ = vFixedDoQ;
