topic Calculating MAX time in script in QlikView

Calculating MAX time in script

Tue, 13 Apr 2010 15:04:06 GMT

Hello Guys,
I'm currently working on a qlikview application and I'm in need of some assistance
I have OrderID's which in turn have their own receiving time and handling time.

Now I would like to know the MAX TAT of every Order ID.

I would like to create a field in my script which calculates my maximum TAT.

The MAX TAT of the OrderID is calculated by subtracting the maximum handling time with the minimum of receiving time.

MAX TAT = max(TIME HANDLED) - min(TIME RECEIVED)

I haven't got a clue how to do this in my script.

ALL help is welcome.

Enclosed you will find a excel sheet explaining my problem visually.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/7853.Problem_5F00_MaxScript.xlsx:550:0]

Calculating MAX time in script

d_pranskus — Tue, 13 Apr 2010 15:12:27 GMT

The solution could be loading your data and then joining to it the agregated data. Something as follows:


Data:
LOAD
 OrderId
 TimeStar,
 TimeEnd,
 ....

LEFT JOIN (Data)
LOAD
 OrderId,
 Max(TimeEnd) - Min(TimeStart) AS Duration
RESIDENT Data
GROUP BY OrderId;

Cheers

Darius

Calculating MAX time in script

Tue, 13 Apr 2010 15:19:10 GMT

Hi, after having a look to your excel, you will have to load the orders again to calculate the max hour. The code probably would look like this:

Load OrderID,

hour( max(time([TIME RECEIVED], 'hh:mm') ) - max(time([TIME HANDLED], 'hh:mm') ) ) as DeltaHours

resident Orders

group by OrderID;

Hope this helps

Regards

Calculating MAX time in script

Tue, 13 Apr 2010 15:47:49 GMT

thanks Mr. Pranksus,

your option worked out great !

Thanks for the help guys

Cheers,