Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Consildating Table Data

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:

NameDateTask NameHours
JP11/07/2012Process Project 16
JP11/07/2012Process Project 21
JP11/07/2012OOO1
JP12/07/2012Process Project 14
JP12/07/2012Process Project 23
JP12/072012Admin2

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

NameDateTotalTimeLogged
JP11/07/20128
JP12/07/20129

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

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

4 Replies
Highlighted
Partner
Partner

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

Highlighted

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

View solution in original post

Highlighted
Not applicable

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

Highlighted
Not applicable

Hi Miguel

Thanks that is super.

Cheers

Jp