Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Thank you very much - i can't believe i missed that. Thanks again for the quick reply.