Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
DATE#(left(publish_time,10),'YYYY-MM-DD') AS Publish_date
this will give you the date, use this in master calender to get all the related value
time#(mid(publish_time,12,19),'hh:mm:ss') as publish_time
this will give you time. use this to convert it to the time of the day according to your classification of morning evening and night.
try like this:
Load *, Year(left(replace(publish_time,'T',' '),19)) as Year, Month(left(replace(publish_time,'T',' '),19)) as Month, WeekDay(left(replace(publish_time,'T',' '),19)) as Dayname, Time(left(replace(publish_time,'T',' '),19)) as Time, 'Q' & Ceil(Month(left(replace(publish_time,'T',' '),19))/3) AS CalendarQuarter; LOAD * INLINE [ publish_time 2017-11-10T07:38:29.00Z ];
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'.
TmpTimeOfDay: LOAD dual(ToD,Start -1+ IterNo()) as TimeOfDay Inline [ Start, End, ToD 0,5, Night 6,11, Morning 12,20, Afternoon 20,23, Night ] while Start -1 + IterNo() <= End ; LOAD 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] INLINE [ publish_time 2019-01-06T07:38:29.000Z 2019-01-06T07:28:54.000Z 2019-01-05T01:00:22.000Z 2019-07-03T14:18:54.000Z 2019-01-03T17:28:54.000Z ] ; Drop table TmpTimeOfDay