To Split Time and Date

[Jojo17]

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. 

[pradosh_thakur]

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.

[Frank_Hartmann]

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
];

[Vegar]

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