Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Calculation of Out of Service Duration

Dear all,

Please help us in deriving thislogic. We would be thank full

Let me explain the scenario,

This is for ATM Down Time calc.

One ATM may be out ofservice(down time) for many reasons

ATM(Terminal ddd) data as a sample is attached for your ref;

In data u will find that thefirst fault started let say for example

TermId

 

FaultStartTime

 

FaultEndTime

 

ddd

 

11/13/11  0:00

 

11/13/11  5:39

 

ddd 

11/13/11  5:31

 

11/13/11  5:39

 

ddd

 

11/13/11  8:37

 

11/13/11  10:19

 

ddd

 

11/13/11  10:06

 

11/13/11  10:19

 

ddd

 

11/13/11  19:32

 

11/14/11  0:00

 

Fault1 started @ 12 AM(0:00) andfixed @ 5:39 AM

Fault2  started @ 5:31 amand fixed @ 5:39 AM (here what is required is any number fault’s which has astart time which is less the fault 1 end time will not be considered) the reason is assuming  that  the subseqwent faults occured because of fault1.

If fault2 end time is greaterthan the fault 1 end time then we have to calculate  as

(fault2 end time  - fault 1start time) as out of service time.

Now if fault 3 has anyoverlapping with fault(1,2) end time then same as above

Or

if fault 3 has any nooverlapping with the earlier faults the it should be consider as new fault

I hope I made myself clear onthis, if any further clarification id write to me.

1 Solution

Accepted Solutions
MVP
MVP

Re: Calculation of Out of Service Duration

I think you just need to go through your list of faults twice, one time with ascending Timestamp, one time with descending Timestamp (In attached sample, I used RecID for order, but now I think it might be better to use the FaultStartTime.).

Then use peek() function and some comparisons to apply your logic. Like this:

INPUT:

LOAD Recno() as RecID,

     TermId,

     FaultStartTime,

     FaultEndTime

FROM

TestData.xls

(biff, embedded labels, table is Sheet1$);

INTERMED:

LOAD *,

if(TermId=Peek(TermId), if(FaultStartTime>peek(FaultEndTime), FaultStartTime),FaultStartTime) as StartTime

resident INPUT order by RecID asc;

drop table INPUT;

INTERMED2:

LOAD *,

if(TermId=Peek(TermId), if(FaultEndTime<peek(FaultEndTime) and peek(FaultStartTime)<FaultEndTime, peek(FaultEndTime),FaultEndTime),FaultEndTime) as EndTime 

resident INTERMED order by RecID desc;

drop table INTERMED;

RESULT:

LOAD Rowno() as FaultID, TermId, StartTime as FaultStartTime, EndTime as FaultEndTime resident INTERMED2 where not isnull(StartTime);

drop table INTERMED2;

2 Replies
MVP
MVP

Re: Calculation of Out of Service Duration

I think you just need to go through your list of faults twice, one time with ascending Timestamp, one time with descending Timestamp (In attached sample, I used RecID for order, but now I think it might be better to use the FaultStartTime.).

Then use peek() function and some comparisons to apply your logic. Like this:

INPUT:

LOAD Recno() as RecID,

     TermId,

     FaultStartTime,

     FaultEndTime

FROM

TestData.xls

(biff, embedded labels, table is Sheet1$);

INTERMED:

LOAD *,

if(TermId=Peek(TermId), if(FaultStartTime>peek(FaultEndTime), FaultStartTime),FaultStartTime) as StartTime

resident INPUT order by RecID asc;

drop table INPUT;

INTERMED2:

LOAD *,

if(TermId=Peek(TermId), if(FaultEndTime<peek(FaultEndTime) and peek(FaultStartTime)<FaultEndTime, peek(FaultEndTime),FaultEndTime),FaultEndTime) as EndTime 

resident INTERMED order by RecID desc;

drop table INTERMED;

RESULT:

LOAD Rowno() as FaultID, TermId, StartTime as FaultStartTime, EndTime as FaultEndTime resident INTERMED2 where not isnull(StartTime);

drop table INTERMED2;

Not applicable

Calculation of Out of Service Duration

Hi swuehl

Thank you for reply and logic, It  is same as what i was expecting .

I would be troubling you for few more things like this I

Thanks & Regards

Venkatesh Salla



Community Browser