topic Grouping records with timestamp into 30 min intervals in QlikView

Grouping records with timestamp into 30 min intervals

villegasi03 — Fri, 18 Mar 2011 23:51:36 GMT

I've got a table with sales transcations which contain timestamps for when the transaction was completed. I am trying to group these records into 30 min intervals. Ultimately I would like to be able to graph the sales throughout a day. Any help would be much appreciated. Below is the table I am working with.

Grouping records with timestamp into 30 min intervals

Miguel_Angel_Baeyens — Sat, 19 Mar 2011 00:33:13 GMT

Hello Mike,

When you do you master calendar, use something like the following

LET vMinDate = Num(Date('01/03/2011'));LET vMaxDate = Num(Date('31/03/2011')); CalendarTemp:LOAD DayStart(TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1))) AS Date, TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1)) AS CompleteTimeStamp, Time(MakeTime(Hour(TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1))), Div(Minute(TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1))), 30) * 30), 'hh:mm') AS HalfHourTimeAUTOGENERATE 1439 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

Is that field HalfHourTime what you are looking for?

Grouping records with timestamp into 30 min intervals

villegasi03 — Sat, 19 Mar 2011 00:55:24 GMT

Miguel,

Thank you for the rely. The field is a timestamp containing the Date(YYYY-MM-DD) and Time(hh:mm:ss:nano secs). Below is the script I'm currently running.

BTicketItems:
LOAD
BCreateDate,
BDiscAmt,
BHiddenTax,
BPrice,
BItemAutoDisc,
BItemGrossPrice,
BItemManulDisc,
MONEY(BItemNetPrice) AS BItemNetPrice,
//BTktItemCloseDate,
FLOOR(BTktItemCloseDate) AS BTktItemCloseDate,
Year(FLOOR(BTktItemCloseDate)) as BYear,
Month(FLOOR(BTktItemCloseDate)) as BMonth,
Date(Monthstart(FLOOR(BTktItemCloseDate)), 'MMM-YYYY') as BMonthYear,
Day(FLOOR(BTktItemCloseDate)) as BDay,
Week(FLOOR(BTktItemCloseDate)) as BWeek,
Weekday(FLOOR(BTktItemCloseDate)) as BWeekday,
'Q' & Ceil(Month(FLOOR(BTktItemCloseDate))/3) as BQuarter ,
BEmployeeID,
BMenuItemID,
BTktItemRevCenterID,
BTktItemVoidTktID,
BTicketID,
BTicketItemID,
BTktItemActEmpID,
BVoidItemID,
BItem
FROM
QlikView\Locations\BTicketItems.qvd
(qvd);