Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner
Partner

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.

View solution in original post

2 Replies
Partner
Partner

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.

View solution in original post

Not applicable

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