Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
skr002244
Contributor III
Contributor III

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

I Have the following table :

Ima_1.PNG

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

Ima_2.PNG

Any help would be much appreciated.

17 Replies
skr002244
Contributor III
Contributor III
Author

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

];


Capture.PNG

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
Contributor III
Contributor III
Author

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 :

T.PNG

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor
Contributor

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

Jayasharma89_0-1710763648813.png

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