topic Consildating Table Data in QlikView

Consildating Table Data

Mon, 30 Jul 2012 11:54:43 GMT

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

Re: Consildating Table Data

giakoum — Mon, 30 Jul 2012 12:00:06 GMT

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

Re: Consildating Table Data

Miguel_Angel_Baeyens — Mon, 30 Jul 2012 12:04:06 GMT

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

Re: Consildating Table Data

Mon, 30 Jul 2012 12:21:59 GMT

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

Re: Consildating Table Data

Mon, 30 Jul 2012 12:23:11 GMT

Hi Miguel

Thanks that is super.

Cheers