topic Re: min date - max date 8hr working hour in QlikView

min date - max date 8hr working hour

Fri, 25 Oct 2013 11:14:18 GMT

Hi Experts

I am loading in from all excel files held in a folder, there are many sheets. I am looking to calculate the end to end time of an application [Process Instance] based on min(Previous start date) - Max(Current date)

The issuess I have is that there are mulitple occurances of each Process Instance and the dates could be accross many files please see sample below :

Report 1

Previous start date Process Instance Tray Current date

1/1/13 9:00 1234 Create 1/1/13 9:30

1/1/13 10:00 ABCD Review 1/1/13 10:12

1/1/13 9:30 1234 Review 1/1/13 11:12

1/1/13 11:12 1234 Post 1/1/13 12:00

1/1/13 10:12 ABCD Post 1/1/13 14:00

Report 2

Previous start date Process Instance Tray Current date

1/1/13 12:00 1234 Doc 2/1/13 9:30

1/1/13 14:00 ABCD Post 1/1/13 16:00

The results we'd like is a colum with duration to date :

Process Instance duration to date

1234 8:30

ABCD 6:00

Can anyone help me with this please ?

Thanks

Re: min date - max date 8hr working hour

sujeetsingh — Fri, 25 Oct 2013 11:17:49 GMT

You can derive a column in script as

=Interval(peek(Field,1) -Max(Field),'d') as DifferenceHours

Re: min date - max date 8hr working hour

Fri, 25 Oct 2013 11:29:20 GMT

Hi sujeetsingh,

Its coming back with an error

Invalid expression

lENDERS:

can you advise if I input it incorrectly ?

Interval

(peek(Prev_Txn_Date,1) -Max(Curr_Txn_Date),'d') as DifferenceHours,

Re: min date - max date 8hr working hour

Jason_Michaelides — Fri, 25 Oct 2013 12:34:16 GMT

Try:

temp:

LOAD * FROM Report1...;

CONCATENATE (temp)

LOAD * FROM Report2...;

Data:

LOAD

[Process Instance]

,Max(Current date) - Min(Previous start date) AS TotalDuration

RESIDENT temp

GROUP BY [Process Instance];

Hope this helps,

Jason

Re: min date - max date 8hr working hour

Tue, 29 Oct 2013 09:39:04 GMT

Hi Jason,

It still doesnt seem to be working for me : Below is the script I am using and then the results Plus what I would have expected to be the results .

[SQL]

let path_Alles = '\\vs2-alpfc2\bkcCOPBUSSUP\BusinessSupport\Weekly to Monthly\Load\*.xls';
for each File in filelist (path_Alles)

lENDERS:
LOAD
*,
applymap('Team',Curr_Activity_Description,null()) as CurrTeam,
applymap('SubTeam',applymap('Team',Curr_Activity_Description,null()),null()) as Team,
applymap('Team',Prev_Activity_Description,null()) as PreTeam,
Filename() as File

FROM
[\\vs2-alpfc2\bkcCOPBUSSUP\BusinessSupport\Weekly to Monthly\Load\*.xls] (biff, embedded labels);

let path_Alles = '\\vs2-alpfc2\bkcCOPBUSSUP\BusinessSupport\Weekly to Monthly\NAPS\*.xls';
for each File in filelist (path_Alles)

concatenate (lENDERS) LOAD
*

FROM

(biff, embedded labels, header is 2 lines, filters(
Remove(Row, Pos(Top, 24)),
Remove(Row, Pos(Top, 23)),
Remove(Row, Pos(Top, 22)),
Remove(Row, Pos(Top, 21))
));

Data:

LOAD

[Process_instance],
Max(Curr_Txn_Date) - Min(Prev_Txn_Date) AS TotalDuration

RESIDENT lENDERS

GROUP BY [Process_instance];

[/SQL]

Then the results :

Process_instance	Curr_Activity_Description	Curr_Txn_Date	Prev_Activity_Description	Prev_Txn_Date	TotalDuration	DURATION SHOULD BE : Based on 24hour & including weekend
10003333	Decision	29/08/2013 16:02:35 .	Begining	29/08/2013 10:22:43 .	06:07:03	318:07:00
10003333	Decision	29/08/2013 10:22:43 .	Begining	29/08/2013 10:22:43 .	06:07:03	318:07:00
10003333	Automatic	29/08/2013 16:02:35 .	Decision	29/08/2013 16:02:35 .	06:07:03	318:07:00
10003333	Call Off	11/09/2013 16:29:45 .	Booking	11/09/2013 16:29:45 .	06:07:03	318:07:00

The result being 11/09/2013 16:29:45 minus 29/08/2013 10:22:43 based on 24 hour day and including weekends. Any guidance you may give would really be appreciated

Thanks

Re: min date - max date 8hr working hour

Tue, 29 Oct 2013 11:48:09 GMT

Jason,

Im so embarrassed - you're solution works, I had the format as Time rather than Interval - however it is absed on 24 hours - is there any way to alter so its based on working day ?

Thanks

Re: Re: min date - max date 8hr working hour

Jason_Michaelides — Tue, 29 Oct 2013 11:54:10 GMT

What do you mean, "based on working day"? I should have wrapped the final expression in Interval() to get the hours and minutes for you - is that what you mean?

Data:

LOAD

[Process Instance]

,Interval( Max(Current date) - Min(Previous start date) ) AS TotalDuration

RESIDENT temp

GROUP BY [Process Instance];

Re: min date - max date 8hr working hour

Tue, 29 Oct 2013 12:04:33 GMT

Hi Jason,

I mean based on 8 hours Mon to Fri rather than the 24 hour clock.

So currently , The code as used - If I have Previous 9am Mon 1st Jan and current as 10am Tues 2nd jan my total Duration is 25hrs - but I would like it as 9 hours as in 1 full 8 hour working day ( monday) and the 1 hour on Tuesday.

Using the same logic for 9am Fri 1st Jan and current as 10am Mon 2nd jan be 9 hours rather than 73 hours

Do you know if there is like in excel a workday function ?

Thanks again for your help

Re: min date - max date 8hr working hour

Jason_Michaelides — Tue, 29 Oct 2013 13:46:22 GMT

I have done this before sing a combination of NetworkDays() and DayEnd() etc. Got quite involved and I'd need to think about it some more.

Basically you have to

use NetWorkDays(Min(Previous start date),Max(Current date)) to get the number of business days the interval touches.
Use DayEnd()-Time to get time in the first and last days.
Take the total days from step 1 and multiply by 8

Or something like that....I'll look in more detail if I find some time later.

Hope this helps,

Jason