Duplicate Hours After Joining Tables in Looker

Looker Q&A Looker Studio
,
1

Hi!

I am facing an issue in Looker when I join two tables: one contains employee emails and their respective manager emails (in case of multiple managers, there are multiple rows for the same employee), and the other contains employee training hours.

Problem:
When I join these tables by email to calculate the total training hours, the result becomes inflated. In my table without the join, the total training hours for all employees is correct. However, after joining the tables, the total increases. I think Looker is summing the hours for each row, which leads to a duplicate count of hours for employees who have multiple managers.

Example of Access Table:
The access table looks like this:

employee1@example.com manager1@example.com
employee1@example.com manager2@example.com
employee1@example.com manager3@example.com
manager1@example.com manager2@example.com
manager1@example.com manager3@example.com
manager2@example.com manager3@example.com

As Manager 3, I need to be able to view data from Employee1, Manager1, and Manager2.

Table for Training Hours:
In the second table, I track training hours for each employee, including the hours attended and other relevant data. The issue arises when I try to join this table with the access table and calculate the total training hours.

Questions:

  • Why does this duplication of hours occur after the join?
  • How can I fix the join to ensure the hours are correctly aggregated without duplication?
  • Would it help if I restructure the access table so that each employee has only one row, with multiple manager columns, rather than multiple rows?

Any advice or suggestions on how to handle this issue would be greatly appreciated!

Thank you!

2

Hi @mpibanez ,

In your Access Table, you have multiple rows for each employee because they have multiple managers. Each row represents a manager-employee relationship.

In your Training Hours Table, you have one row per employee (assuming the training hours are linked to each employee’s email).When you join the two tables, Looker will multiply the rows from the Access Table by the corresponding rows from the Training Hours Table, creating duplicate rows for employees who have multiple managers. Each of these duplicates has the same training hours data, which results in inflated totals for training hours.

If employee1@example.com has 3 managers (manager1, manager2, and manager3), and each manager row is joined with the training data for that employee, the training hours will be counted 3 times for that employee instead of once.

3

Hi Nandha,
Thanks for the clarification. Do you know how I can fix that? I tried using the AVG function in the metric, but it still shows the wrong result.
Thanks again