Rounding timestamp to hour

[venkatg6759]

Hello Everyone,

I have a field called readtimestamp% . we have some books where people read we get timestamp. we need to find how many people read per  hour on a day.

It has something like

02/02/2014  01:07:00  

02/02/2014  00:00:14

How can i round them to

02/02/2014  01:00:00

02/02/2014  00:00:00

Thanks,

Venkata

[eduardo_sommer]

I suggest you to change the round() function to floor(). Look in your example that 8:35 pm is being rounded to 21 instead of 20.

Eduardo

[johnca]

Yes, but it is > halfway to the next hour. The original example did not go beyond the 30 minute point. If that is the original author's intent than yes, use floor() instead of round().

Good point.

[venkatg6759]

I am able to get the timestamp rounded to Hour, but the problem here is it was giving me straight line (reads per day)

instead of variations per hour.

Let me paste the script for you .

LOAD

[ConsumptionStatsByReader.%Publication GUID] as [%Publication GUID],

     [ConsumptionStatsByReader.Consumption Date] as [ConsumptionCalendar.Consumption Date],

     [ConsumptionStatsByReader.%Contact GUID] as [%Contact GUID],

     If( [ConsumptionStatsByReader.Readership Medium] = 'Other', 'GS360',  [ConsumptionStatsByReader.Readership Medium] ) as [Readership Medium],

     //[ConsumptionStatsByReader.%Alert Id] as [%Alert Id],

     [ConsumptionStatsByReader.Reads By Contact] as [Reads By Contact]

FROM

$(vAggrFiles)ConsumptionStatsByReader.qvd (qvd);

Qualify *;

ConsumptionCalendar:

load Date As [Consumption Date],

     Year,

     //MaxYear,

     Quarter,

     Month,

     Day,

     //[Week Day],

     //Hour,

     //Minute,

     [%TIMESTAMP],    

     //[Month Id],

     //[Month and Year],

     [Fiscal Week]

     //[YTD Flag],

     //[QTD Flag],

     //[MTD Flag],

     //[WTD Flag],

     //[T12M Flag],

     //[T6M Flag],

     //[T8W Flag]

from [\\firmwide.corp.gs.com\irroot\Projects\NY\BI\Qlikview\qvd\General\Calendar.QVD] (qvd);

Timestamp coming from calender Qvd which is linked by Date

[johnca]

Three things...

What is the format of your field [Consumption Date]? Post an example of what an actual [Consumption Date] value is.

I suspect your calendar has all dates, but not all minutes of each hour for each day, and your data does.

What if you just format the date field in your data as suggested rather than try to link a calendar? What does it look like?

[Anonymous]

Have you tried your "TRY" with my suggestion?  See attached.

[Sokkorn]

Hi,

Above solution may answer to your question, but I just want to share something with you.

***If your field is string data type, then we use:

=Date(Date#('02/02/2014 01:07:00','dd/MM/yyyy hh:mm:ss'),'dd-MM-yyyy hh:00:00') -> Return 02-02-2014 01:00:00

=Date(Floor(Date#('02/02/2014 01:07:00','dd/MM/yyyy hh:mm:ss')),'dd-MM-yyyy') -> Return 02-02-2014

=Time(Floor(Frac(Date(Date#('02/02/2014 01:07:00','dd/MM/yyyy hh:mm:ss'),'dd-MM-yyyy hh:00:00')),1/24),'hh:mm:ss') -> Return 01:00:00

***If your field is timestamp data type, then we can use

=Date(TimeStampField,'dd-MM-yyyy hh:00:00') -> Return 02-02-2014 01:00:00

=Date(Floor(TimeStampField),'dd-MM-yyyy') -> Return 02-02-2014

=Time(Floor(Frac(Date(TimeStampField,'dd-MM-yyyy hh:00:00')),1/24),'hh:mm:ss') -> Return 01:00:00

Regards,

Sokkorn

[venkatg6759]

I posted the qvw.How can i get the graph show variation as per hour.

Can someone help me out.

Thanks,

Venkata

[eduardo_sommer]

Hi, Venkata

The problem is in your data model. The table that contains the [Reads by Contact] field has a day key, while you are trying to aggregate by a more granular dimension that exist only in the other table.

You need to maintain yor [Reads by Contact] in a timestamp level and link the tables also by the timestamp field.

Eduardo

[johnca]

Your Date field does not contain the hours. You cannot create what is not there (in this case).

Does your data even have a field where the timestamp contains the time of day?

[rogerioqv]

Venkata,

The problem is in the format of date / time. Check the script so you get the correct format to be able to apply the expression.

See the attached image.