topic Re: To Split Time and Date in QlikView

To Split Time and Date

Jojo17 — Sat, 16 Nov 2024 21:27:21 GMT

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.

Re: To Split Time and Date

pradosh_thakur — Thu, 07 Feb 2019 09:20:23 GMT

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.

Re: To Split Time and Date

Frank_Hartmann — Thu, 07 Feb 2019 09:38:07 GMT

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

Re: To Split Time and Date

Vegar — Thu, 07 Feb 2019 12:16:28 GMT

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