topic Re: Urgent - How to extract and add data from different fields? in QlikView

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

Mon, 10 Dec 2012 23:03:17 GMT

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

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

Anonymous — Tue, 11 Dec 2012 03:22:32 GMT

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.

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

sujeetsingh — Tue, 11 Dec 2012 04:36:27 GMT

A good question by Sunden

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

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

Mon, 17 Dec 2012 20:39:57 GMT

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