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.

[sunny_talwar · Accepted solution]

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

];

[skr002244]

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

[sunny_talwar]

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

];

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]

Thank you Sunny for the help

[sunny_talwar]

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]

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 ?

[sunny_talwar]

You mean in the first case?

[sunny_talwar]

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

];

[skr002244]

I meant in this case.