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.
bfournet
New Contributor II

Time rouding

Hello,

I would like to round time this way :

From original timestamp
Half hour
QuarterTime by 5 minutes steps
21:18:0021:0021:1521:15
21:39:0021:3021:3021:35
21:56:0021:3021:4521:55

Rounded values should never be superior to originale timestamp.

So possible values can be :

     * Half hour -> 21:00, 21:30, 22:00, 22:30 ...

     * Quarter -> 21:00, 21:15, 21:30, 21:45 ...

     * Time by 5 minute step -> : 21:00, 21:05, 21:10, 21:15 ...

I created a master calender this way (Field "Horodatage" is a timestamp) :

Calendrier:

NoConcatenate

LOAD Distinct

    Horodatage, // Original timestamp

    MakeDate(Year(Horodatage), Month(Horodatage), Day(Horodatage)) as Date,

    Num(Hour(Horodatage), '00') & 'h' as Hour,

    Time(Class(Time(Horodatage),0.5/24), 'hh:mm') as Half_Hour,

    Time(Class(Time(Horodatage),0.5/2/24),'hh:mm') as Quarter,

    Time(Class(Time(Horodatage),0.5/6/24),'hh:mm') as Time_By_5

Resident Calendrier_TMP;

But i recently noticed that sometimes rounding is correct and sometimes not (yellow):

Do you have an idea why it doesn't work here? Do you have a better calculation method ?

Thank you by advance !

3 Replies

Re: Time rouding

Perhaps:

Time(Ceil(frac(Horodatage),1/48)) as Half_Hour

Time(Ceil(frac(Horodatage),1/96)) as Quarter_Hour


talk is cheap, supply exceeds demand
martin_dideriks
New Contributor III

Re: Time rouding

You could do like this:

Time(Round(MyTime, (1/24/12))) as [5 minutes],

  Time(Round(MyTime, (1/24/4))) as Quarter,

  Time(Round(MyTime, (1/24/2))) as HalfHour

You can use Round,Floor or Ceil depending on your needs.

//Martin

Re: Time rouding

LOAD *,

     Time(Floor(Frac(Horodatage),'00:30:00')) as Half_Hour,

     Time(Floor(Frac(Horodatage),'00:15:00')) as Quarter,

     Time(Floor(Frac(Horodatage),'00:05:00')) as Time_By_5

Inline [

Horodatage

02/01/2014 21:18:00

02/01/2014 21:39:00

02/01/2014 21:56:00

];

regards

Marco

Community Browser