topic Re: round date with time difference in QlikView

round date with time difference

userid128223 — Thu, 01 Jun 2017 16:39:09 GMT

I have 2 dates in table:

StartDate: 2017-01-20 00:01:25 345

EndDate: 2017-01-20 00:01:26 235

How can i get the difference between EndDate - StartDate & round it off to 2 decimals.

Thanks

Re: round date with time difference

antoniotiman — Thu, 01 Jun 2017 16:49:34 GMT

Hi,

SET TimestampFormat='YYYY-MM-DD hh:mm:ss fff';

= Interval(EndDate-StartDate,'ss,ff') -> 00,89

Regards,

Antonio

Re: round date with time difference

userid128223 — Thu, 01 Jun 2017 20:28:01 GMT

I am getting wierd results when i try below.

interval(EndDate - StartDate ,'ssss') as Date_difference;

it is not calculating seconds difference in some fields properly, I also get E-5 on many fields.

Re: round date with time difference

kamal_sanguri — Fri, 02 Jun 2017 05:28:13 GMT

Hi,

Please refer this document. Let me know in case you have any doubt.

Re: round date with time difference

userid128223 — Fri, 02 Jun 2017 08:36:53 GMT

thanks for the script. how do i use to evaluate seconds that exceeds 30 or only show records when it exceeds 5 minutes based on your answer.

Re: round date with time difference

kamal_sanguri — Fri, 02 Jun 2017 08:51:00 GMT

try this:

=if (Time(EndDate-StartDate,'hh:mm:ss fff')>Time#('00:00:30:','hh:mm:ss'),Time(EndDate-StartDate,'hh:mm:ss fff'),'Less than 30 seconds not recorded.')

I have set it show when difference is more than 30 seconds, you can change it per your needs like for five minutes:

=if (Time(EndDate-StartDate,'hh:mm:ss fff')>Time#('00:05:00:','hh:mm:ss'),Time(EndDate-StartDate,'hh:mm:ss fff'),'Less than 30 seconds not recorded.')

Re: round date with time difference

userid128223 — Fri, 02 Jun 2017 15:08:22 GMT

i am looking to do this in script with where clause.

Re: round date with time difference

kamal_sanguri — Mon, 05 Jun 2017 05:11:49 GMT

Hi,

Please find attached document where I have put same condition in script with Where clause to show records where difference of time is more than 30 sec (you can change as per your needs.

Data:

LOAD Time#(EndDate,'YYYY-MM-DD hh:mm:ss fff') as EndDate,

Time#(StartDate,'YYYY-MM-DD hh:mm:ss fff') as StartDate;

Load * Inline

[

StartDate,EndDate

2017-01-20 00:01:25 235,2017-01-20 00:02:56 235

2017-01-20 00:01:25 235,2017-01-20 00:01:26 235

2017-01-20 00:01:25 235,2017-01-20 00:11:26 235

2017-01-20 01:21:25 235,2017-01-20 01:31:26 235

2017-01-20 01:21:25 235,2017-01-20 01:31:29 235

];

NoConcatenate

NewData:

LOAD *

Resident Data

Where Time(EndDate-StartDate,'hh:mm:ss fff')>Time#('00:00:30:','hh:mm:ss');

DROP Table Data;