Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Jojo17
New 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
pradosh_thakur
Honored Contributor II

Re: To Split Time and Date

 

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
Frank_Hartmann
Honored Contributor

Re: To Split Time and Date

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

Re: To Split Time and Date

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

Please ekskuse my Norglish and Swenglish typos.