how do I instert the qvw here? sorry
SET
ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
headcount:
LOAD
Date(Alt(Date#([Snapshot Month], 'YYYY/MM/DD'),
Date#([Snapshot Month], 'DD/MM/YYYY'),
Date#([Snapshot Month], 'DD/MM/YY'),
Date#([Snapshot Month], 'MM/DD/YYYY'),
Date#([Snapshot Month], 'M/D/YYYY'),
),'MMM-YY') as [Snapshot Month],
[Business Area],
// Functions,
Manco,
LT,
[Employee ID],
[Preferred Forename],
Surname,
[Work Email Address],
[Corporate Title],
[Gender Code],
[Cost Centre],
[Line Manager ID],
[Line Manager Surname],
[Line Manager First Name],
[Manager Level],
[Business Title],
[Accredited Service Date],
[Continuous Service Band],
[Work Location - Country],
[Permanent / FTC],
Count
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Headcount_.xls]
(biff, embedded labels, table is headcount$);
perspectives:
LOAD Perspective_ID,
Perspective,
KPI_ID,
Initiative,
Script,
Target,
Theme_Des,
Phase,
T_Green,
T_Amber,
T_Red
FROM
[\\sinfiler2\Asia_Operations\Dashboard\\PBMScorecard\DataFiles\excel\Dashboard Priority.xlsx]
(ooxml, embedded labels, table is KPI);
customer:
LOAD Cust_Month,
// Theme,
// Team,
// Product,
Definition,
[Current Month],
[Previous Month],
Comments as Cust_comments,
Tgt
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Customer_dashboard.xls]
(biff, embedded labels, table is actuals$);
LOAD Initiative,
//[Business Area],
LT,
[Theme Team],
Product,
KPI,
Definition,
Targets,
Target_Green,
Target_Amber,
Target_Red
FROM
[\\sinfiler2\Asia_Operations\Dashboard\\PBMScorecard\DataFiles\excel\Customer_dashboard.xls]
(biff, embedded labels, table is targets$);
actualbudget:
LOAD Fin_Month,
//Manco,
LT,
[Budget Owner],
[Khalix Node],
[Division],
[Finance Division],
CATM,
CALM,
[MoM Variance],
CBYTD_TM,
CAYTD_TM,
VABYTD,
VABYTD_P,
CFY_Budget,
CFY_Forecast,
VBFFY,
VBFFY_P
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Finance-breakdown.xlsx]
(ooxml, embedded labels, table is Cost_ALL);
LOAD// LT,
[Khalix Node],
// [Finance Division],
HCA_lastmonth,
HCA_thismont,
//F5,
HCB_thismonth,
HCYTD_ThisMonth,
HCYTD_Budget,
HCFull_budget,
HCYTD_Forecast
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Finance-breakdown.xlsx]
(ooxml, embedded labels, table is HC_ALL);
openroles:
LOAD [Aging Actions],
[Aging since Final Approval],
[Role #],
[Role Description / Job Title],
[Line Managers],
[Role Status],
Functions,
[Budget type],
Type,
[Final Approved Date],
[Cost Centre],
// Country,
// [Corporate Title],
// Comments,
[To be filled]
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\openroles_massaged.xls]
(biff, embedded labels, table is Sheet1$);
JML_combined:
LOAD// [Business Area],
Drivers,
// Manco,
// Functions,
LT,
JML_country,
[Employee ID] as JML_EID,
Forename,
Surname as JML_surname,
JML_Effectivedate,
JML_Month,
Voluntary,
[High Performer Flag],
[STT Leaver FTE],
[SubDivision 1 - Moved From],
[SubDivision 2 - Moved From],
[SubDivision 3 - Moved From],
[Old Work Location - Location Code],
[Old Work Location - Country],
ct
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\JML_combined.xls]
(biff, embedded labels, table is JML_combined$);
perspectives:
LOAD Perspective,
Category,
[Employee FTE]
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Perspectives.xlsx]
(ooxml, embedded labels, table is Sheet1);
eperf:
LOAD [Eperf Programme],
[eperf Milestone] as eperf_milestone,
[eperf Capture] as eperf_capture,
[eperf Milestone Deadline],
// Drivers,
// [Business Area],
//Functions,
//[Work Location - Country],
[Employee ID],
//[Employee Name],
//[Preferred Forename],
//Surname,
//"SUM([# Objective Settings Not Started]",
//"SUM([Objective Settings In Progress (with Employee)]",
//"SUM([Objective Settings In Progress (with Manager)])",
//"SUM([# Objective Settings Completed])",
//HC,
[% Completion] as eperf_completion,
"=IF([% Completion]<<1,'no action reqd','investigate')" as eperf_action
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\eperf_action.xls]
(biff, embedded labels, table is eperf_aciton$);
training:
LOAD [Employee ID],
[Employment Status],
[Completion Status],
[Employee Individual Title],
[First Name],
[Last Name],
[Email Address],
[Learning Course],
[Learning Course Name],
[Learning Activity],
[Delivery Method Name],
[Activity Start Date],
[Completion Date],
[Manager Details],
// Division,
// [Sub Division 1],
// [Sub Division 2],
// [Sub Division 3],
// [Sub Division 4],
// [Sub Division 5],
// [Sub Division 6],
// [Sub Division 7],
// [Cost Centre],
[Activity End Date],
[Original Hire Date],
Year_Complete
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\training.xlsx]
(ooxml, embedded labels, table is [2014_training]);
weekly_outstanding:
LOAD
//[Snapshot Month],
[Created Date (Issue)],
[Business Area],
[Organisation Level 6 (Issue)],
[Organisation Level 7 (Issue)],
[Organisation Name (Issue)],
[Location Name (Issue)],
Countid,
[Issue ID],
[Source Reference],
[Issue Type],
[Issue Name],
[Detailed Description (Issue)],
[Impact Rating],
[Issue Status],
flgClosedPendReview,
[Issue Owner Name (Issue)],
[Issue Workflow Owner],
[Last Updated Date (Issue)],
[Accepted Date (Issue)],
[Issue Due],
[Target Completion Date (Issue)],
[Revised Target Completion Date (Issue)],
LastComments,
LAST_UPDATE_USER_RK,
[Catalogue Risk Level 4 (Issue)],
Policy,
[Catalogue Risk Level 5 (Issue)],
[Catalogue Risk Level 6 (Issue)],
DateClosedPendReviewToYes,
[Organisation Level 5 (Issue)],
[Action ID],
[Action Description],
[Action Status],
[Action Owner],
[Current Target Completion Date (Action)],
[Action Dependency],
[Closure Date (Action)],
[Month (Issues)],
[Issues Due Band],
[Month (Action)],
[Action Due],
[Age (PD Action)],
[Action Age Band],
[Date to CPR (Y)],
[CPR Ages]
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Weekly_Outstanding_R!.xls]
(biff, embedded labels, table is Data$);
FinCalendar:
Load Distinct
Week(Fin_Month) as FinWeek,
Year(Fin_Month) as FinYear,
Month(Fin_Month) as FinMonth,
Date(Fin_Month) as FinDate,
Date(Fin_Month,'MMM-YY') as [Snapshot Month] //Added Join
Resident actualbudget;
BISCalendar:
Load Distinct
Week([Snapshot Month]) as BISWeek,
Year([Snapshot Month]) as BISYear,
Month([Snapshot Month]) as BISMonth,
Date([Snapshot Month]) as BISDate,
Date([Snapshot Month],'MMM-YY') as [Snapshot Month] //Added Join
Resident headcount;
CustCalendar:
Load Distinct
Week (Cust_Month) as CustWeek,
Year(Cust_Month) as CustYear,
Month(Cust_Month) as CustMonth,
Date(Cust_Month) as CusDate,
Date(Cust_Month,'MMM-YY') as [Snapshot Month]
Resident customer;
LinkTable:
Load Distinct
[Snapshot Month]
Resident FinCalendar;
Load Distinct
[Snapshot Month]
Resident BISCalendar;
Load Distinct
[Snapshot Month]
Resident CustCalendar;
MasterCalendar:
LOAD
DATE([Snapshot Month],'YYYYMMDD') AS [Snapshot Month],
DATE([Snapshot Month],'YYYYMM') AS ID_TIME_YEARMONTH,
DATE([Snapshot Month],'MMM-YY') AS ID_TIME_YEARMONTHDESC,
DATE([Snapshot Month],'YYYYMMDD') AS ID_TIME_YEARMONTHDAY,
'Q'&ceil(Month([Snapshot Month])/3) AS ID_TIME_QUARTER,
YEAR([Snapshot Month]) AS ID_TIME_YEAR,
MONTH([Snapshot Month]) AS ID_TIME_MONTH,
NUM(MONTH([Snapshot Month])) AS ID_TIME_MONTH#,
NUM(DAY([Snapshot Month])) AS ID_TIME_DAY,
WEEKDAY([Snapshot Month]) AS ID_TIME_DAY_WEEK,
NUM([Snapshot Month]) AS ID_TIME_DATE#
Resident LinkTable;
