Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
bfournet
Partner
Partner

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
Gysbert_Wassenaar

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
Partner
Partner

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

MarcoWedel

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