Urgent - How to extract and add data from different fields?

[#1]

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

[Anonymous]

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.

[sujeetsingh]

A good question by Sunden

First try to make  a straight table... with wat you require

.

[#4 · Accepted solution]

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