Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

skr002244
New Contributor III

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

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

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

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

Highlighted
skr002244
New Contributor III

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

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

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

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

Attaching the file.

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,

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

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

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.

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

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);