Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Capture true time interval (it's a brain teaser)

I am trying to capture a turn time order to completion. I have to subtract out all the time an order was on dealy from the order to completion interval to get an accurate number.

The issue is that there can be multiple delays on and order and they can stop and start at any time. In some cases a multiple dealys may start before a previous delay is completed. I can capture the turn time of the delay entry to completion but I want to be able to subtract out the over lapping time if that makes sense.

The solution may not be QlikView specific. There may be a way to do this in SQL? I am including an example of one order with multiple delays to illustrate the problem.

Thanks

2 Replies
Not applicable

Hi David,

i would suggest the following load script to calculate the "Real" delay time each delay has. For this to work you must ensure that the records are sorted by ORDER_CDE and by start time:

Directory;



LOAD

ORDER_CDE,

ORDER_DELAY.ENTERED_DTE

,

ORDER_DELAY.CMPLTD_DTE

,

[Delay Turn Time]

,

[Order to completion Turn Time]

,

//Check with previous record

If(previous(ORDER_CDE)=ORDER_CDE,

IF

(ORDER_DELAY.ENTERED_DTE < previous(ORDER_DELAY.CMPLTD_DTE),

IF



(ORDER_DELAY.CMPLTD_DTE < previous(ORDER_DELAY.CMPLTD_DTE),0,

ORDER_DELAY.CMPLTD_DTE



-previous(ORDER_DELAY.CMPLTD_DTE)),

ORDER_DELAY.CMPLTD_DTE



-ORDER_DELAY.ENTERED_DTE),

ORDER_DELAY.CMPLTD_DTE

-ORDER_DELAY.ENTERED_DTE) as RealDelayTime

FROM

example.xls

(

biff, embedded labels, table is Sheet1$);







Regards,

Joao

Not applicable

Thanks, let me play around with this and see what I can come up with