Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to join table on two dimensions

I am a relative new Qlikview developer and looking for a bit of help on a particular problem.

I am trying to join two tables based on two dimensions, technican and date.   I have a date table that lists all of a technicians jobs completed over a month of time and it includes a TECH ID as well as DATE COMPLETED.   Over a month each technician will have hundreds of completed jobs, each with a unique ID.


I also have a table with timesheet data.   This timesheet has:


TECH ID

SCHEDULE DATE

PRODUCTIVE HOURS

OVERTIME HOURS

In order to accurately count a particular technicians utilization i need to be able to add up the number of jobs a technican completed in a particular day and the divide by the number of productive and overtime hours in that day.

For a selected Date and tech => count(PCAD)/(sum(Productive Hours)+sum(Overtime Hours))

I tried to join the tables using Tech ID and Date but am not getting any results for the number of hours.

I have included an .XLS sheet below with some fake data and my initial .QVW

Thank you very much

1 Solution

Accepted Solutions
fosuzuki
Valued Contributor II

Re: How to join table on two dimensions

Adjust your first LOAD:

LOAD [Date Completed] as Date,

     Tech_Completed,

     [Tech ID Completed] AS [Tech ID],

     Address,

     [Job Type],

     PCAD

FROM

[Hours Trial.xlsx]

(ooxml, embedded labels, table is [Fake Jobs]);

the join was wrong because the Tech ID field names were not the same in both tables.

2 Replies
fosuzuki
Valued Contributor II

Re: How to join table on two dimensions

Adjust your first LOAD:

LOAD [Date Completed] as Date,

     Tech_Completed,

     [Tech ID Completed] AS [Tech ID],

     Address,

     [Job Type],

     PCAD

FROM

[Hours Trial.xlsx]

(ooxml, embedded labels, table is [Fake Jobs]);

the join was wrong because the Tech ID field names were not the same in both tables.

Not applicable

Re: How to join table on two dimensions

Thank you very much - i can't believe i missed that.   Thanks again for the quick reply.

Community Browser