Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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

Tags (3)
1 Solution

Accepted Solutions

Re: Consildating Table Data

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 Replies
giakoum
Honored Contributor II

Re: Consildating Table Data

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

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

Not applicable

Re: Consildating Table Data

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

Not applicable

Re: Consildating Table Data

Hi Miguel

Thanks that is super.

Cheers

Jp