Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

To Split Time and Date

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. 

publish_time.PNG

3 Replies
Highlighted
Master II
Master II

 

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.

Learning never stops.
Highlighted
Master II
Master II

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

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

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes