Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Morning,
I have created a dashboard and would like to link the following tables but I am currently getting an error. ServiceWarriors are not reflecting on my views. The link key between ServiceWarriors table and eGain Productivity is "ServiceWarrior" and that consist of FirstName and LastName. I concatenated the field an gave it an alias of ServiceWarrior. I also created join key under ServiceWarriors which consists of ServiceWarrior &'_'& MEASURE_DATE as SW_LINK_eGain_Prod and it is supposed to join with ServiceWarrior &'_'& MEASURE_DATE as SW_LINK_eGain_Prod from eGain Productivity.
When running the script below which doesnot look ok as it on includes a separator and MeasureDate
My Table Viewer looks OK but if link key above is not correct the dashboard won't function properly. See my table viewer below:
Below are the scripts I am using:
1st Script (Connection to DB)
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Workforce;Data Source=03RNB-AQM05\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=03RNB-QLKVW01;Use Encryption for Data=False;Tag with column collation when possible=False];
2nd Script (Calender)
Let varMinDate = Num(makedate(2014,4,01));
Let varMaxDate = Num(makedate(2015,3,31));
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS MEASURE_DATE,
Year(TempDate) As Year,
Month(TempDate) As Month,
if(day(TempDate) > 25, Month(addmonths(TempDate,1)), Month(TempDate)) as ReportMonth,
1 as WarriorCalenderLink
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
3rd Script ServiceWarriors:
// Export from: C:\Work\Scripts\KPI2015Query.sql
left join
ServiceWarriors:
SQL SELECT DISTINCT ContactNo,
ContractExpiryDate,
CostCode,
CreateBy,
CreateDate,
"DateOfBirth_Calc",
Department,
EmpActive,
EmplContractType,
EmployeeNumber,
Employer,
Ethnicity,
EwfmID,
Exclude,
ExtAgencyArea,
ExtAgencyName,
ExtAgencyRegion,
ExtendedLeave,
ExtLeaveFrom,
ExtLeaveTo,
ExtraComments,
FirstName,
Gender,
HireDate,
IbsUserNumber,
ID,
IdNumber,
"IfOther_(LineManager)",
JitterRecommended,
JobTitle,
LastName,
FirstName +' '+ LastName as ServiceWarrior,
LineManager,
LineManagerEmail,
Location,
Manager,
ModBy,
ModDate,
NewRecruit,
ProgressionShift,
ReasonForExclusion,
ResignationCode,
ResignationDate,
ResignationDetails,
Secondment,
SecondmentDetails,
SeniorManager,
Skill as SM_Skill,
StartOnFloorDate,
SubmissionDue,
SuburbTownshipFrom,
Transfered,
UipUsername,
WorkEmail
FROM Workforce.dbo.Employee
WHERE EmpActive = 1
AND Exclude = 0
AND (Skill = 'GM Queries' Or Skill = 'Service Recovery - General' Or Skill = 'Social Media' OR Skill = 'Chat Support General' Or Skill = 'Written Co - General')
AND EmpActive=1
AND Exclude = 0;
ServiceWarriors:
NoConcatenate
load *,
ServiceWarrior &'_'& MEASURE_DATE as SW_LINK_eGain_Prod,
IbsUserNumber & '_' & MEASURE_DATE as SW_LINK_CSAT,
IbsUserNumber & '_' & MEASURE_DATE as SW_LINK_BRE,
IbsUserNumber & '_' & MEASURE_DATE as SW_LINK_KA,
IbsUserNumber & '_' & MEASURE_DATE as SW_LINK_PendingJournals
resident MasterCalendar;
drop table MasterCalendar;
4th Script (eGain Productivity):
//Combine data from the Activity, User and Queue reporting tables to eliminate synth keys
METRICS_ACTIVITY:
LOAD
//BRAVO_DATE,
Date(MakeDate(Year(BRAVO_DATE), Month(BRAVO_DATE), Day(BRAVO_DATE)), 'YYYY/MM/DD') as MEASURE_DATE,
Interval(Hour(BRAVO_DATE) &':00', 'hh:mm') as DATE_HOUR,
Interval(Round(Hour(BRAVO_DATE) &':'& Minute(BRAVO_DATE), (1/48)), 'hh:mm') as DATE_HALF_HOUR,
DEPT_ID as DEPARTMENT_ID,
USER_ID,
QUEUE_ID,
RESOLVE_TIME
From \\03rnb-qlkvw01\D\QVDepoly\eGain\eGain Data Loads\qvd_EGMLR_SMY_ACTIVITY.qvd(qvd);
Concatenate (METRICS_ACTIVITY)
LOAD
//BRAVO_DATE,
Date(MakeDate(Year(BRAVO_DATE), Month(BRAVO_DATE), Day(BRAVO_DATE)), 'YYYY/MM/DD') as MEASURE_DATE,
Interval(Hour(BRAVO_DATE) &':00', 'hh:mm') as DATE_HOUR,
Interval(Round(Hour(BRAVO_DATE) &':'& Minute(BRAVO_DATE), (1/48)), 'hh:mm') as DATE_HALF_HOUR,
USER_ID,
ACTIVITIES_COMPLETED
From \\03rnb-qlkvw01\D\QVDepoly\eGain\eGain Data Loads\qvd_EGPLR_SMY_USER.qvd(qvd);
Concatenate (METRICS_ACTIVITY)
//METRICS_QUEUE:
LOAD
//BRAVO_DATE,
Date(MakeDate(Year(BRAVO_DATE), Month(BRAVO_DATE), Day(BRAVO_DATE)), 'YYYY/MM/DD') as MEASURE_DATE,
Interval(Hour(BRAVO_DATE) &':00', 'hh:mm') as DATE_HOUR,
Interval(Round(Hour(BRAVO_DATE) &':'& Minute(BRAVO_DATE), (1/48)), 'hh:mm') as DATE_HALF_HOUR,
COMPLETED_MAILS,
DEPT_ID as DEPARTMENT_ID,
EXISTINGCASES_MAILS,
NEWCASES_MAILS,
NOCASES_MAILS,
QUEUE_ID,
XFER_IN,
XFER_OUT
From \\03rnb-qlkvw01\D\QVDepoly\eGain\eGain Data Loads\qvd_EGMLR_SMY_QUEUE.qvd(qvd);
Concatenate (METRICS_ACTIVITY)
//User details
[Users]:
LOAD
USER_ID
,USER_NAME
,(FIRST_NAME&' '&LAST_NAME) as ServiceWarrior
,[MANAGER_ID]
From \\03rnb-qlkvw01\D\QVDepoly\eGain\eGain Data Loads\qvd_EGPL_USER.qvd(qvd);
Final_Draft:
Load
// MEASURE_DATE
// ServiceWarrior
ServiceWarrior &'_'& MEASURE_DATE as SW_LINK_eGain_Prod
,DEPARTMENT_ID
,USER_ID
,QUEUE_ID
,RESOLVE_TIME
,ACTIVITIES_COMPLETED
,COMPLETED_MAILS
,EXISTINGCASES_MAILS
,NEWCASES_MAILS
,NOCASES_MAILS
,XFER_IN
,XFER_OUT
Resident METRICS_ACTIVITY;
Drop Table METRICS_ACTIVITY;
Exit Script;
Please assist as your assistance will be greatly appreciated. I need to present the Dashboard end of business today.
Thanking you in advance.
KInd Regards,
Elijah