topic Re: Find out Total Time Taken by each Project in QlikView

Find out Total Time Taken by each Project

MK_QSL — Tue, 08 Jul 2014 15:18:50 GMT

1) NEED SOLUTION IN SCRIPT ONLY

2) TOTAL TIME Taken by Each Sub_Project also needed in Minutes

I need to find out Total Time Taken by each project..... i.e. X and Y

Project	Sub_Project	ProjectID	StartTime	EndTime
X	X.A	1001	01/07/2014 10:00:00	02/07/2014 00:00:00
X	X.A	1002	02/07/2014 00:00:00	03/07/2014 00:00:00
X	X.B	1003	02/07/2014 11:00:00	02/07/2014 14:00:00
X	X.A	1004	03/07/2014 00:00:00	03/07/2014 22:00:00
X	X.C	1005	03/07/2014 21:00:00	03/07/2014 23:30:00
Y	Y.B	1006	05/07/2014 14:00:00	06/07/2014 00:00:00
Y	Y.A	1007	05/07/2014 22:00:00	06/07/2014 00:00:00
Y	Y.A	1008	06/07/2014 00:00:00	07/07/2014 00:00:00
Y	Y.B	1009	06/07/2014 00:00:00	07/07/2014 00:00:00
Y	Y.A	1010	07/07/2014 00:00:00	08/07/2014 00:00:00
Y	Y.B	1011	07/07/2014 00:00:00	08/07/2014 00:00:00
Y	Y.A	1012	08/07/2014 00:00:00	09/07/2014 00:00:00
Y	Y.B	1013	08/07/2014 00:00:00	08/07/2014 16:00:00
Y	Y.A	1014	09/07/2014 00:00:00	10/07/2014 00:00:00
Y	Y.C	1015	09/07/2014 14:00:00	09/07/2014 16:00:00
Y	Y.A	1016	10/07/2014 00:00:00	10/07/2014 10:00:00

Also, to be noted, that any Sub_Projects falling in same time should not be calculated twice.

i.e.

Time taken by 1003 is already covered in 1002 so would not be counted...

Time taken by 1005 is actually 150 min but due to 60 mins already covered in 1004, will be counted as 90 min

Time taken by 1008 is completely matching with 1009, so only one of them would be counted , same with 1010 and 1011

1013 is covered in 1012, so will give 0 time

and so on...

Re: Find out Total Time Taken by each Project

maxgro — Tue, 08 Jul 2014 16:49:09 GMT

my attempt (hope to understand the question)

the idea is to generate every minute with iterno and then count distinct

Re: Find out Total Time Taken by each Project

martynlloyd — Tue, 08 Jul 2014 17:18:28 GMT

Hi,

SubProject:

LOAD
Project,
Sub_Project,
ProjectID,
Interval(EndTime-StartTime, 'mm') as Duration_SP
from ... ;

Project:
LOAD
Project,
Sum(Duration_SP) as Duration
Resident SubProject
Group By Project;

ML.

Re: Find out Total Time Taken by each Project

martynlloyd — Tue, 08 Jul 2014 17:27:58 GMT

Oops. Sorry, should be Interval#

And, of course, you will need to convert the duration back to a time to display (1=24hrs, 0.25=6hrs, etc), using Interval or Time, and your chosen format.

Re: Find out Total Time Taken by each Project

PradeepReddy — Tue, 08 Jul 2014 17:52:09 GMT

Hi Manish,

Please find the attached application, it might be helpful.

Regards,

Pradeep

Re: Find out Total Time Taken by each Project

rbecher — Tue, 08 Jul 2014 18:03:07 GMT

Hi Manish,

if you do not have gaps in between the time span the project duration would be:

LOAD
Project, 
timestamp(Min(StartTime)) as StartTimePrj,
timestamp(Max(EndTime)) as EndTimePrj,
Interval(Max(EndTime) - Min(StartTime)) as Duration
Resident Data
Group By Project;

- Ralf

Re: Find out Total Time Taken by each Project

jagan — Wed, 09 Jul 2014 04:08:15 GMT

Hi Manish,

Try like this

ProjectDetails:

LOAD

FROM DataSource;

ProjectDuration:

LOAD

Project,

Interval(Max(EndTime) - Min(StartTime), 'D') as DaysTaken

Resident ProjectDetails

Group By Project;

Regards,

Jagan.

Re: Find out Total Time Taken by each Project

martynlloyd — Wed, 09 Jul 2014 09:28:40 GMT

Hi Manish,

I believe this will solve your problem?

SubProject

LOAD Project,
     Sub_Project,
     ProjectID,
     StartTime,
     EndTime,
Interval(EndTime-StartTime, 'mm') as Duration_SP
FROM
[..\Community.xlsx]
(ooxml, embedded labels, table is Sheet1);

Project:
LOAD
Project,
Min(StartTime),
Max(EndTime),
Interval(Max(EndTime)-Min(StartTime))
Resident SubProject
Group By Project;

Regards..

Re: Find out Total Time Taken by each Project

MK_QSL — Wed, 09 Jul 2014 09:57:43 GMT

Thanks for your reply.. .I am testing all replies...