Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

skr002244
New 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.

1 Solution

Accepted Solutions

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

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,

  DayEnd(B + IterNo() - 1) as Temp,

  DayStart(B + IterNo() - 1) as Temp2

While DayEnd(B + IterNo() - 1) <= DayEnd(C);

LOAD * Inline [

A, B, C

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

];

Capture.PNG

16 Replies

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

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,

  DayEnd(B + IterNo() - 1) as Temp,

  DayStart(B + IterNo() - 1) as Temp2

While DayEnd(B + IterNo() - 1) <= DayEnd(C);

LOAD * Inline [

A, B, C

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

];

Capture.PNG

skr002244
New Contributor III

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

Thank you Sunny. This is the exact solution which i was looking for.

I do have another query. Suppose the same row had to be split into hours.

Example - 10/7/2015 11:30:00 PM - 10/7/2015 11:59:59 PM

                10/8/2015 12:00:00 PM - 10/8/2015 01:00:00 PM

                 .......

                 .......

                10/8/2015 6:00:00 AM - 10/8/2015 07:00:00 AM

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

How about 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(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

skr002244
New Contributor III

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

Thank you Sunny for the help

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

Not a problem. If now you have got all you wanted, I would suggest you to close the thread down by marking the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

skr002244
New Contributor III

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

Yes,sunny i would certainly do that. I had one last query. As you had mentioned,the time period in my scenario is ending at 11:59:59. If i want to showcase it in terms of complete 60 mins instead of  59 mins what changes do i have to do to the existing code to achieve that ?

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

You mean in the first case?

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

Try this:

Table:

LOAD A,

  TimeStamp(Round(RangeMax(Temp2, B), 1/86400)) as B,

  TimeStamp(Round(RangeMin(Temp, C), 1/86400)) as C,

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

LOAD A,

  B,

  C,

  DayEnd(B + IterNo() - 1) as Temp,

  DayStart(B + IterNo() - 1) as Temp2

While DayEnd(B + IterNo() - 1) <= DayEnd(C);

LOAD * Inline [

A, B, C

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

];

Highlighted
skr002244
New Contributor III

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

I meant in this case.