Consildating Table Data

[#1]

Hi All

I need some help consolidating some data that is being loaded from an excel spreadsheet.

The spreadsheet contains peoples time logging activitives.

an example of the spreadsheet looks like:

Name	Date	Task Name	Hours
JP	11/07/2012	Process Project 1	6
JP	11/07/2012	Process Project 2	1
JP	11/07/2012	OOO	1
JP	12/07/2012	Process Project 1	4
JP	12/07/2012	Process Project 2	3
JP	12/072012	Admin	2

fields in the script are:

Name

Date

Task

TimeSpent

The issue I need to solve is to be able to draw a graph of FTE equivalent for each project.

So the formula should be for any specific task on any day: time spent on task / total time logged for the day, which will give me the portion of time someone spent on the particular task for the day.

So I was thinking I would create a new table linked on name and date that looks like below

Name

Date

TotalTimeLoggedForDay

Name	Date	TotalTimeLogged
JP	11/07/2012	8
JP	12/07/2012	9

If anyone can explain how I would create the new table from the existing table to produce a result as described above that would be much appreciated.

Thanks

JP

[giakoum]

Why do you need a seperate table? You need to do this during reload? A chart can easily be created with what you want.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

To get that table, assuming the field names you have provided in the post, it should be something like the following:

FTE:
LOAD Name,
     Date,
     Sum(TimeSpent) AS TotalTimeLoggedForDay
RESIDENT TableWithTasks
GROUP BY Name, Date;

If you can replace the RESIDENT by a FROM qvd file the result will be quite faster.

Hope that helps.

Miguel

[#4]

Hi Ioannis

Thanks for the speedy reply. I figured it could be done in a chart but couldn't figure out what the formula should be, because as you make selections on the date or task the hours logged will be available but I couldn't figure out how to get the total hours.

Any suggested formulas?

Thanks

Jp

[#5]

Hi Miguel

Thanks that is super.

Cheers

Jp