How to find the time difference in minutes with respect to different days ?

[skr002244]

I Have the following table :

I want the difference in time (Mins) for each day. As shown in the below image.

Any help would be much appreciated.

[skr002244]

I meant in this case

Table:

LOAD A,

  TimeStamp(RangeMax(Temp2, B)) as B,

  TimeStamp(RangeMin(Temp, C)) as C,

  Interval(RangeMin(Temp, C) - RangeMax(Temp2, B), 'mm') as D;

LOAD A,

  B,

  C,

  TimeStamp(Ceil(B + MakeTime(IterNo() - 1), 1/24)) as Temp,

  TimeStamp(Floor(B + MakeTime(IterNo() - 1), 1/24)) as Temp2

While Ceil(B + MakeTime(IterNo() - 1), 1/24) <= Ceil(C, 1/24);

LOAD * Inline [

A, B, C

7_266, 10/07/2015 11:30:00 PM, 10/08/2015 7:00:00 AM

];

But I guess the issue is that for 10/07 we are getting 30 minutes. But isn't it suppose to be 30 mins from 11:30 to 12:00 is 30 mins or even to 11:59:59 is 30 mins - 1 second. We want to penalize it by the whole minute? I guess I can force it to be 29 mins, but you have to decide if that is correct.

Best,

Sunny

[sunny_talwar]

It is already showing based on 60 minutes in this case.

[skr002244]

But there is a deviation in the result. If i run this specific code. by changing the starting time stamp to  12:00:00 AM the code doesn't provide me with the proper result.

The code used :

Table:

LOAD A,

  TimeStamp(RangeMax(Temp2, B)) as B,

  TimeStamp(RangeMin(Temp, C)) as C,

  Interval(RangeMin(Temp, C) - RangeMax(Temp2, B), 'mm') as D;

LOAD A,

  B,

  C,

  TimeStamp(ceil(B + MakeTime(IterNo() - 1),1/24)) as Temp,

  TimeStamp(floor(B + MakeTime(IterNo() - 1),1/24)) as Temp2,

  IterNo() As I

While ceil(B + MakeTime(IterNo() - 1),1/24) <= Ceil(C,1/24);

LOAD * Inline [

A, B, C

7_266, 10/07/2015 12:00:00 AM, 10/08/2015 7:10:00 AM

];

The result for the above code is :

The expected result should give me the total minutes within each hour duration.

Suppose 12:00:00 AM - 7:10:00 AM

The result should be

12:00:00 AM-1:00:00 AM - 60 mins

1:00:00 AM-2:00:00 AM - 60 mins

...

...

7:00:00 AM-7:10:00 AM - 10 mins

And then it should stop at 7:10:00 AM. The new rows should not be generated after 07:10:00 AM.

[skr002244]

Sorry the time stamps are 10/07/2015 12:00:00 AM, 10/07/2015 7:10:00 AM.

Attaching the file.

[sunny_talwar]

Try this:

Table:

LOAD A,

  TimeStamp(RangeMax(Temp2, B)) as B,

  TimeStamp(RangeMin(Temp, C)) as C,

  Interval(RangeMin(Temp, C) - RangeMax(Temp2, B), 'mm') as D;

LOAD A,

  B,

  C,

  TimeStamp(Ceil(If(B = Round(B, 1/24), B + 1/24, B) + MakeTime(IterNo() - 1), 1/24)) as Temp,

  TimeStamp(Floor(B + MakeTime(IterNo() - 1), 1/24)) as Temp2

While Ceil(B + MakeTime(IterNo() - 1), 1/24) <= Ceil(C, 1/24);

LOAD * Inline [

A, B, C

7_266, 10/07/2015 12:00:00 AM, 10/07/2015 7:10:00 AM

];

[skr002244]

Sorry for the delay in replying back.

Sunny, i think you should look at the entire data which i'm using for which i'm applying the code.There are some deviations.

There are minutes in negative.

In one particular case %key-296. There is a mismatch in From & To date time calculation and also the minutes calculation.

[sunny_talwar]

So basically you want to get rid of any negative times, right? Try this:

Table:

LOAD %Key,

  TimeStamp(RangeMax(Temp2, B)) as B,

  TimeStamp(RangeMin(Temp, C)) as C,

  Interval(RangeMin(Temp, C) - RangeMax(Temp2, B), 'mm') as D

Where RangeMin(Temp, C) - RangeMax(Temp2, B) > 0;

LOAD %Key,

  B,

  C,

  TimeStamp(Ceil(If(B = Round(B, 1/24), B + 1/24, B) + MakeTime(IterNo() - 1), 1/24)) as Temp,

  TimeStamp(Floor(B + MakeTime(IterNo() - 1), 1/24)) as Temp2

While Ceil(B + MakeTime(IterNo() - 1), 1/24) <= Ceil(C, 1/24);

LOAD %Key,

    B,

    C

FROM

[Data.qvd]

(qvd);

[Jayasharma89]

Hi All 

Please help me to resolve the issue i want to calculate rapid scans that happens with in one minute of each other..

this is the field from which i have to calculate rapid scans

where on the same day the scans happened within one minute of each other that will be rapid scan

write now i using 
if (Actual Scan Time=Peek(Actual Scan Time),1,0) as Same time.

but i am not getting the correct output...please help me