Select all and replace the script with the provided script.
SUB calendarization
TRACE Working on master Calendar;
// 1- Check for & Load existing calendar QVDs (can we work incrementally?)
CALL load_base_table ('time_range', '$(time_range_TableName)','DateTime')
CALL load_base_table ('date_time', '$(date_time_TableName)','_date_time_link_incr')
// 2- Find first and last date from my data
Range:
LOAD
DayStart(min) as startdate,
DayStart(max) as enddate,
timestamp(max) as maxLogTimeStamp;
LOAD
min(LogEntryPeriodStart) as min,
//max(LogTimeStamp) as max
now(1) as max
resident working;
let startdate = floor(peek('startdate',-1,'Range'));
let enddate = ceil(peek('enddate',-1,'Range')) +1;
Trace 'Current Endate ' & $(enddate);
Let enddate = If($(enddate) > Today(1) + 1, Today(1) +1,$(enddate));
Trace 'Current Endate ' & $(enddate);
let maxLogTimeStamp = peek('maxLogTimeStamp',-1,'Range');
Let maxLogTimeStamp_Hour = hour(maxLogTimeStamp);
Let hour_now = maxLogTimeStamp_Hour;
Drop Table Range;
// SORT ORDERING of Time fields
// To sort backward from now(reload) -- for 24-Hour summary charts
hour_temp:
mapping Load
recno()-1 & ':00' as Hour,
if($(hour_now)-(recno()-1)>=0, $(hour_now)-(recno()-1),23+($(hour_now)-(recno()-1))+1) as hour_sort
autogenerate (24);
// Establish order of weekdays
Weekdays:
Load
weekday(weekstart(today())+recno()-1) as Weekday,
recno() as weekday_sort
autogenerate 7;
// For all non-24-hour Summary charts, we want "normal" numeric sorting of Hour from 0 to 23 hours
Hour_Table:
NoConcatenate
Load
rowno()-1 & ':00' as Hour,
rowno()-1 & ':00' as [Hour of Day]
AutoGenerate (24);
// Build a time-date table of every minute between my start and end date
DO WHILE startdate < enddate
time_range_working:
LOAD
timestamp($(startdate) + (1/(1440))*(recno()-1),'YYYY-MM-DD h:mm') as DateTime_temp // "_temp" for incremental
autogenerate (1440);
//let startdate = num($(startdate) + 1,'###0.#####','.') ;
let startdate = $(startdate) + 1;
Trace 'Current date ' & $(startdate);
LOOP
Inner Join (time_range_working)
IntervalMatch (DateTime_temp)
Load
LogEntryPeriodStart-(1/(1440)) AS start_minus_one,
LogTimeStamp
Resident working;
date_time_working:
Load
*,
(Round(Num(start_minus_one+1/(1440)),1/1440)&'|'
&Round(Num(LogTimeStamp),1/1440)) as _date_time_link_incr_temp // LINK w/ LogContent; "_temp" for incremental loading
RESIDENT time_range_working;
// Concatenate base (historical) calendar tables for dates within cutoffdate
CALL concat_tables ('time_range', 'time_range_working','DateTime')
CALL concat_tables ('date_time', 'date_time_working','_date_time_link_incr')
// Store then drop time_range; date_time table stored after summaries loaded (below)
CALL store_files ('time_range', '$(time_range_TableName)')
DROP TABLE time_range;
Drop field start_minus_one;
Let NoOfRows_date_time = NoOfRows('date_time');
ENDSUB
SUB calendarization_add
TRACE Looking for additional date time links to include in DateTime;
//Concatenate date_time_link fields that do not already exist in date_time. This can happen due to rounding of Timestamps
// Note that the added _date_time_link entries will not include the interval in the calendar as do the first-pass entries.
// The totals will still be accurate, but when viewed by minute or hour, a small percentage of sessions will only appear when the session
// started, whereas the majority will show how the session spanned multiple minutes or hours.
Concatenate (date_time)
Load
_date_time_link as _date_time_link_incr,
LogEntryPeriodStart as DateTime_temp
Resident LogContent
Where Not Exists([_date_time_link_incr],[_date_time_link]);
Let NoOfRows_date_time_additional = NoOfRows('date_time')-$(NoOfRows_date_time);
TRACE $(NoOfRows_date_time_additional) new _date_time_links added.;
CALL store_files ('date_time', '$(date_time_TableName)') // Store this table without all the extra columns to limit the QVD size on disk.
TRACE Adding additional fields to date_time table.;
REM This must be done after the "Summary" loads because those alter the _date_time_link field;
date_time_link:
NoConcatenate Load
Distinct _date_time_link_incr AS _date_time_link,
DateTime_temp AS DateTime,
MonthName(DateTime_temp) AS Month,
WeekStart(DateTime_temp) as [Week Beginning],
WeekDay(DateTime_temp) as Weekday,
makedate(year(DateTime_temp),month(DateTime_temp),day(DateTime_temp)) as Date,
Hour(DateTime_temp)&':00' as Hour,
Time(DateTime_temp) as Time,
ApplyMap('hour_temp',Hour(DateTime_temp)&':00' ) as hour_sort,
Minute(DateTime_temp) as [Minute of Hour],
timestamp(floor(DateTime_temp,1/(24)),'MMM-DD hh:00') as [Hour Timeline],
timestamp(floor(DateTime_temp,10/(1440)),'MMM-DD hh:mm') As [Ten-Minute Timeline],
Hour(Frac(DateTime_temp))&':'&right(0&Minute(floor(Frac(DateTime_temp),1/144)),2) As [Ten-Minute Generic Timeline],
timestamp(floor(DateTime_temp,1/(1440)),'MMM-DD hh:mm') as [One-Minute Timeline],
If(DateTime_temp>=$(vLast4Hours),1) AS last4hours,
If(DateTime_temp>=$(vLast24Hours),1) AS last24hours,
If(DateTime_temp>=$(vLast72Hours),1) AS last72hours
Resident date_time
ORDER BY DateTime_temp DESC;
DROP TABLE date_time;
// Create Timeframe field for quick selection of common historical timeframes
Last:
Load Distinct [Hour Timeline], 'Last 4 Hours' as [Timeframe] Resident date_time_link Where last4hours=1;
Concatenate Load Distinct [Hour Timeline], 'Last 24 Hours' as [Timeframe] Resident date_time_link Where last24hours=1;
Concatenate Load Distinct [Hour Timeline], 'Last 72 Hours' as [Timeframe] Resident date_time_link Where last72hours=1;
Drop fields last4hours,last24hours,last72hours;
ENDSUB
