Data Linking Issue

Fri, 17 Apr 2015 08:03:02 GMT

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

Re: Data Linking Issue

Anonymous — Fri, 17 Apr 2015 15:39:02 GMT

I believe the problem is here, you're getting null value:

FirstName +' '+ LastName as ServiceWarrior,

"+" is for the numeric operations. You have to use "&":

FirstName &' '& LastName as ServiceWarrior,

Re: Data Linking Issue

Mon, 20 Apr 2015 10:38:08 GMT

Hi Michael, The script with +' '+ is for SQL script which I am connecting to SQL server and I am getting an error when trying to use & on my script to concatenate the two fields.

Re: Data Linking Issue

Anonymous — Mon, 20 Apr 2015 11:37:59 GMT

Yes, + is for SQL, & is for QV. Can you upload your application?

Re: Data Linking Issue

Tue, 21 Apr 2015 07:21:09 GMT

Morning Michael, How do I load the application when replying? Please advise?

Re: Data Linking Issue

Tue, 21 Apr 2015 11:59:55 GMT

There is one more thing I just noticed: Is qlikview case sensitive with regards to field names?

From eGain I have (FIRST_NAME &' '& LAST_NAME) and I am linking to ServiceWarrior (FirstName &' '& LastName).

Re: Data Linking Issue

Anonymous — Tue, 21 Apr 2015 12:00:50 GMT

In the "Reply" box, find "Use advanced editor" - it is on the top right, light blue on the gray background. When you click it, there will be "Attach" on the bottom right. Click it and browse to your file.

Please don't attach large files, try to keep it under 1MB. You can reduce if needed (make selections, go to File - Reduce Data - Keep Possible Values) and scramble data (Document Properties - Scrambling) if you have to.

Re: Data Linking Issue

Tue, 21 Apr 2015 13:42:30 GMT

Hi Michael,

I just checked and I don't have that option. see attached

Re: Data Linking Issue

Anonymous — Tue, 21 Apr 2015 14:54:56 GMT

I have no idea...

You have to contact site admin for help...

Re: Data Linking Issue

Thu, 30 Apr 2015 07:48:02 GMT

Morning Michael, Please note that I managed to sort out the linking issue. It was caused by Concatenate function that I used on "4th Script (eGain Productivity)". The dashboard is now linking properly. Thanks.

Re: Data Linking Issue

Anonymous — Thu, 30 Apr 2015 12:17:00 GMT

Good to know you've fixed the problem.

Still wondering why you can't upload the files (?)

topic Re: Data Linking Issue in QlikView

Data Linking Issue

Re: Data Linking Issue

Re: Data Linking Issue

Re: Data Linking Issue

Re: Data Linking Issue

Re: Data Linking Issue

Re: Data Linking Issue

Re: Data Linking Issue

Re: Data Linking Issue

Re: Data Linking Issue

Re: Data Linking Issue