Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
TASK | 1st Tech-ID | 1st Tech-Hrs | 2nd Tech-ID | 2nd Tech-Hrs | 3rd Tech-ID | 3rd Tech-Hrs |
A | 1 | 3.20 | 3 | 1.40 | 5 | 0.75 |
B | 3 | 1.30 | 0 | 0.00 | 0 | 0.00 |
C | 5 | 2.20 | 3 | 0.75 | 0 | 0.00 |
D | 17 | 5.00 | 5 | 3.00 | 0 | 0.00 |
The table shows the amount of hours per task invested by 3 Tech's. For example, to complete TASK A three technicians were required: 1, 3 and 5 and the total amount of hours to complete TASK A was : 5.35 hours.
I need to consolidate the amount of hours per technician in the following table and create a bar chart:
Tech-ID (Dimension) | Total Hours |
1 | 3.20 |
3 | 3.45 |
5 | 5.95 |
17 | 5.00 |
What would be the best way of doing this?
Thanks
Hi,
Sorry for the delay in my response, you gave me a good idea however I had to do some research.
To provide more insight in regards to the table format, I have to say that we are managing the process in MS SharePoint so I am exporting to the table to Access and then doing an ODBC with Qlikview. I really don't want to mess up with the SP process at this point so ... I had to found a way to work with that "particular" table.
Going back to the solution I used "Concatenate":
SQL Select
Task, 1st Tech-ID as Tech, 1st Tech-Hrs as Hours
From Table;
Concatenate (Table)
SQL Select
Task. 2nd Tech-ID as Tech, 2nd Teck-Hrs as Hours
From Table;
Concatenate (Table)
SQL Select
Task. 3rd Tech-ID as Tech, 3rd Teck-Hrs as Hours
From Table;
Thanks
Hi Jose,
That is a strange table format. What happens if there is a need for more than 3 technicians for a task?
Anyway, in this case I would probably load the table in three load statements (Since the table structure is the same, these three tables will automatically be concatenated into one table in your datamodel):
Task, Tech1 as Tech, Tech1Hours as Hours
+
Task, Tech2 as Tech, Tech2Hours as Hours
+
Task, Tech3 as Tech, Tech3Hours as Hours
Then it'll be easy to create charts and calculating the total time spent for each tech.
A good question by Sunden
First try to make a straight table... with wat you require
.
Hi,
Sorry for the delay in my response, you gave me a good idea however I had to do some research.
To provide more insight in regards to the table format, I have to say that we are managing the process in MS SharePoint so I am exporting to the table to Access and then doing an ODBC with Qlikview. I really don't want to mess up with the SP process at this point so ... I had to found a way to work with that "particular" table.
Going back to the solution I used "Concatenate":
SQL Select
Task, 1st Tech-ID as Tech, 1st Tech-Hrs as Hours
From Table;
Concatenate (Table)
SQL Select
Task. 2nd Tech-ID as Tech, 2nd Teck-Hrs as Hours
From Table;
Concatenate (Table)
SQL Select
Task. 3rd Tech-ID as Tech, 3rd Teck-Hrs as Hours
From Table;
Thanks