Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Time range problems

Hi,

I am working on a qvw where I have to display time durations and time ranges.

What I have available in the data source are a Start_Time field and an End_Time field in DD.MM.YYY hh:mm:ss

So far I have successfully  created two duration fields by adding this to the script:

Interval(Time(End_Time) - (Start_Time),'hh:mm:ss') as Duration

Interval(Time(End_Time) - (Start_Time),'ss') as Duration_seconds

No I want to create a time range field which should show the number of elements in a chart with durations of, let´s say

less than 1 hour,

1-3 hours,

everything else

I totally failed with the Duration field, that´s why I created the above Duration_Seconds field as well.

My last useless effort looks like this:

if((Duration_seconds)<=3600,'less than 1 hour',

  if((Duration_seconds)<=10800,'1-3hours',

  'everything else')) as Range

The result is that all records fall into the less than 1 hour category, even if they lasted 8.000 seconds or more.

I am lost...

Is the seconds field in a wrong format? Can it be converted into numerical? Can it work with the Duration field?

Thanks.

1 Solution

Accepted Solutions

Re: Time range problems

For your Duration_seconds try :

    (Time(End_Time) - (Start_Time))  * 24  * 60 * 60    as Duration_seconds


A timestamp is held as the number of days since a certain date ages ago.  The integer part of it is the number of days since then and the decimal part is the fraction of a day.  It is held as a dual and the text representation is the standard human readable format.

The 'ss' argument to your interval() formats the dual text representation to be human readable seconds, but the dual number part remains as a decimal.

So to get the number of seconds multiply the decimal by the number of seconds in a day, which is 24*60*60..



2 Replies

Re: Time range problems

For your Duration_seconds try :

    (Time(End_Time) - (Start_Time))  * 24  * 60 * 60    as Duration_seconds


A timestamp is held as the number of days since a certain date ages ago.  The integer part of it is the number of days since then and the decimal part is the fraction of a day.  It is held as a dual and the text representation is the standard human readable format.

The 'ss' argument to your interval() formats the dual text representation to be human readable seconds, but the dual number part remains as a decimal.

So to get the number of seconds multiply the decimal by the number of seconds in a day, which is 24*60*60..



Not applicable

Re: Time range problems

Bill,

Thank you very much, it works perfectly!

Cheers!

Community Browser