The data set I have, looks at analyzing the publish time and date for popular videos.
My data set looks like this and how best can I write my script to obtain the following:
1. Year 2. Month 3. Day of the week (will I need a separate set of info for this as I only have 'day' available) 4. Time of Day (morning, afternoon, night) 5. To classify them in 4 quarters of the year
The eventual outcome I would like to have is to analyze peak and shoulder: quarters > months > day > time, to analyze the data and do a drill down to translate this into more meaningful and actionable steps.
You do not need to manipulate the timestamp. QlikView will handle the format correctly so there is no need of trimming away the 'T' or '000Z'.
LOAD dual(ToD,Start -1+ IterNo()) as TimeOfDay Inline [
Start, End, ToD
while Start -1 + IterNo() <= End
Timestamp(publish_time) as Publishtime,
year(publish_time) as Year,
Month(publish_time) as Month,
WeekDay(publish_time) as DayOfWeek,
num(Hour(publish_time) ) as TimeOfDay,
QuarterName(publish_time) as QuarterName,
'Q' & Ceil(Month(publish_time)/3) AS [Quarter]
Drop table TmpTimeOfDay