Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

TASK1st Tech-ID1st Tech-Hrs2nd Tech-ID2nd Tech-Hrs3rd Tech-ID3rd Tech-Hrs
A13.2031.4050.75
B31.3000.0000.00
C52.2030.7500.00
D175.0053.0000.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
13.20
33.45
55.95
175.00

What would be the best way of doing this?

Thanks

1 Solution

Accepted Solutions
Not applicable

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

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

3 Replies
jsn
Honored Contributor

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

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
Honored Contributor III

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

A good question by Sunden

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

.

Not applicable

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

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

Community Browser