Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I've been using Qlik Sense now for 2 weeks and i'm very pleased with the functionality and flexibility of the program. Since the community is a good place to find the right answers i'm going to use it as much as possible (only if needed).
So i've been searching in the forums to find the right command to create me a 24 hour view of activity. So far i'm using the regular MasterCalendar which can be found in the community and it's working great. I've tried quite a few lines of code to create me the 24h time, but so far i only get 00:00:00 time. I'm using Unix timestamp and the data looks like: MM/DD/YYYY at HH/MM/SS - Month/Day/Year at Hour/Minute/second and in unix it looks something like 1446717600 aka 11/05/2015 at 10:00:00. Anyway to the point now:
I want my graph to look something like this:
It would be even better to have it with every half an hour. (00:00, 00:30, 01:00, 01:30 and so on).
So my master calendar is like that:
Temp:
Load
min(Date) as minDate,
max(Date) as maxDate
Resident Actions;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS Date,
time(Frac(TempDate)) as Time,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
the Red text is what i use right now, allthough it's not working.
HI there, sorry am busy on previous days,,
yup
> first you convert unix timestamp dates to normal qlikview understandable timestamp dates in your source data (RAW data or transaction data)
> Create master calendar along with the 30min and hourly buckets whatever you req, and then connect your source converted timestamp date field to master calendar timestamp field like below :
//Step 1
SOURCE_DATA:
Load blabladimension,
Value,
timestamp(UnixTimeStampField/ 86400 + 25569) as TransactionDate,
timestamp(UnixTimeStampField/ 86400 + 25569) as %CalendarTimestampKey
from blablapath.
//Step2
MaxMinDates:
LOAD
Max(TransactionDate) AS MaxDate,
Min(TransactionDate) AS MinDate
Resident SOURCE_DATA;
;
LET varMinDate = floor(num(Date(Peek('MinDate'))));
LET varMaxDate = floor(num(Date(Peek('MaxDate'))));
//LET varMinDate = num(makedate(2015,11,1));
//LET varMaxDate = num(makedate(2015,11,2));
MASTER_CALENDAR_STAGE1:
LOAD
Date($(varMinDate)+IterNo()-1) AS Date,
if(even(rowno()),floor(rand()*4+7),floor(rand()*8+3)) as ValueMethod2
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
join
Load distinct Interval,Time5,rn,hi;
Load *,rn,hi,
Time(if(len(Time5)>0,rangesum(peek('Interval'),Time5),Time5),'HH:mm:ss') as Interval;
Load if(recno()=1,time#('00:00:00','hh:mm:ss'),time#('00:00:01','hh:mm:ss')) as Time5,
rowno() as rn,RecNo() as hi
AutoGenerate 86400;
MASTER_CALENDAR_FINAL_STAGE:
Load FinalDate as Date_Time_Stamp,
FinalDate as %CalendarTimestampKey,
Time5,
Date(Date) as Date,
Year(Date) as Year,
Month(Date) as Month,
Day(Date) as Day ,Interval,
MIN_30_BUCKET,
HOUR_1_BUCKET,
hour(MIN_30_BUCKET)&':'&num(Minute(MIN_30_BUCKET),'00') as FINAL_30_MIN_BUCKET,
HOur(HOUR_1_BUCKET)&':00' as FINAL_1_HOUR_BUCKET;
Load Date,Timestamp(Date&' '&Interval) as FinalDate,
Interval,
Time5,
Timestamp(Date&' '&maketime(hour(Interval),if(Minute(Interval)>0 and Minute(Interval)<30,0,30),0)) as MIN_30_BUCKET,
Timestamp(Date&' '&maketime(hour(Interval),0,0)) as HOUR_1_BUCKET
Resident MASTER_CALENDAR_STAGE1;
drop table MASTER_CALENDAR_STAGE1;
>>> And in front end make an line chart take the dimension as FINAL_30_MIN_BUCKET
and expression as your sum(Value)
, i have attached an application with dummy data (not unix data ) try and let me know
hi use this
time(frac(<UnixTimeStamp>/ 86400 + 25569)) as Time
timestamp(<UnixTimeStamp>/ 86400 + 25569) as TS,
date(floor(<UnixTimeStamp>/ 86400 + 25569)) as Date,
time(frac(<UnixTimeStamp>/ 86400 + 25569)) as Time,
Hi Prem!
So i replaced the code with some updates and now i keep getting somewhat of an error when im in the debugger mode. As you can see i also added ActionsTemp to the resident field to get the right data there.
Although i must say that everything is being done already with another load (time converting):
Actions:
LOAD
autonumber(action_timestamp & action_newsletter_id & action_email) as clickkey,
autonumber(action_newsletter & action_email) as optinkey,
Date(floor(Makedate(1970,1,1)+ action_timestamp/24/60/60)) as Date,
*
RESIDENT ActionsTemp;
What you suggested:
MasterCalendar:
Load
timestamp(<action_timestamp>/ 86400 + 25569) as TS,
date(floor(<action_timestamp>/ 86400 + 25569)) as Date,
time(frac(<action_timestamp>/ 86400 + 25569)) as Time,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar, ActionsTemp
Order By TempDate ASC;
Drop Table TempCalendar;
What i did myself:
MasterCalendar:
Load
timestamp(TempDate) as TS,
date(floor(TempDate)) as Date,
time(frac(TempDate)) as Time,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Thank you for your reply, since i have alot of data, i will let you know soon enough what happened (loading it in as we speak).
unforunately What i did myself: didn't work how i wanted. so the question still remains open. Meanwhile i'll try to make the amount of data which im loading smaller so i could upload an example
Unfortunately i still haven't quite figured it out yet how to make the time work. The thing is, that i got it working like that:
MasterTime:
LOAD
time(recno()/96,'hh:mm') as Time2
autogenerate(96)
But my master calendar dosen't work anymore. i also read the discussion about the master time table.
I get one line of code ( Time(Floor(Frac(Timestamp),1/24/60),'hh:mm') as Time), but it dosen't quite solve my problem. I must be honest, i'm not very good in Qlik Sense coding yet, but i'm trying real hard to understand it all.
(PS: i cannot load the data up since my qlik sense project is 300mb (took 4 gb data from different files) - maybe someone has a good idea how to seperate data a faster way than to reload all the data once more into the project).
So what should i do if now i have this:
--------------------------------------------------------------------------
Actions:
LOAD
autonumber(action_timestamp & action_newsletter_id & action_email) as clickkey,
autonumber(action_newsletter & action_email) as optinkey,
Date(floor(Makedate(1970,1,1)+ action_timestamp/24/60/60)) as Date,
Time(Floor(Frac(Makedate(1970,1,1)+ action_timestamp/1/24/60),'hh:mm')) as Time, - i want it to be converted to every 5 minute intervals. 09:00, 09:05, 09:010
*
RESIDENT ActionsTemp;
---------------------------------------------------------------------------
Temp2:
Load
min(Time) as minTime,
max(Time) as maxTime
Resident Actions;
Let varMinTime = Num(Peek('minTime', 0, 'Temp2'));
Let varMaxTime = Num(Peek('maxTime', 0, 'Temp2'));
DROP Table Temp2;
TempTimeTable:
LOAD
$(varMinTime) + Iterno()-1 As Num,
Time($(varMinTime) + IterNo() - 1) as TempTime
AutoGenerate 1 While $(varMinTime) + IterNo() -1 <= $(varMaxTime);
MasterTime:
LOAD
time(recno()/96,'hh:mm') as Time2
autogenerate(96)
Resident TempTimeTable
Order By TempTime ASC;
Drop Table TempTimeTable;
Could anyone help me out without me having to upload data in here? Oh and right now i get something like this:
Or if im doing it completely wrong, then would someone give me tips how to correct it all?
First, you need to divide the unix timestamp by 86400, not by (1/24/60 or 24/60/60).
Then you can use the round() function (or floor() or ceil() to round the time values to 5 min bucket values.
I finally use time#(time() function to let QV generate a consistent numeric representation for the key values.
This should get you started:
MasterTime:
LOAD
Time#(Time( 1/(24*12)*(Recno() - 1))) as Time
AutoGenerate 24*12; // 5 min step time values
Actions:
LOAD *,
Time#(Time(Round(ActionTime,1/(24*12)))) as Time;
LOAD
action_timestamp,
Date(floor(Makedate(1970,1,1)+ action_timestamp/24/60/60)) as ActionDate,
Time(Frac(Makedate(1970,1,1)+ action_timestamp/1/24/60),'hh:mm') as ActionTime;
LOAD
(ceil(Rand()*10*24*60*60)+1446163200) as action_timestamp // just generating some unix values
Autogenerate 100;
Thank you for your reply, but it keeps giving me errors: Field not found - <ActionTime>: MasterTime: LOAD Time#(Time(Round(ActionTime,1/(24*12)))) as Time AutoGenerate 24*12 RESIDENT Actions
What i have (i dont think i need to autogenerate some unix values to my action_timestamp, has more than 14 miljon rows):
THIS PART WORKS
Actions:
LOAD *,
autonumber(action_timestamp & action_newsletter_id & action_email) as clickkey,
autonumber(action_newsletter & action_email) as optinkey,
Date(floor(Makedate(1970,1,1)+ action_timestamp/24/60/60)) as Date,
Time(Frac(Makedate(1970,1,1)+ action_timestamp/1/24/60),'hh:mm') as ActionTime,
Time#(Time( 1/(24*12)*(Recno() - 1))) as Time
RESIDENT ActionsTemp;
DROP table ActionsTemp;
THIS PART GIVES ERROR
MasterTime:
LOAD
Time#(Time(Round(ActionTime,1/(24*12)))) as Time
AutoGenerate 24*12 // 5 min step time values
RESIDENT Actions;
Like FFS, ActionTime is in the table Actions, why can't it find it? (other residents work perfectly)
for me it feels like:
Time#(Time( 1/(24*12)*(Recno() - 1))) as Time
and
Time#(Time(Round(ActionTime,1/(24*12)))) as Time
are pretty much the same thing
I would love to get it work :/, and it's complete shite, that my database is so freaking huge that i cannot upload it. (and it's only like 1-2% of the total database). I should have like 2 TB data but am using only 4gb.
Oh, and i must mention that i removed all the previous data:
Temp2:
Load
min(Time) as minTime,
max(Time) as maxTime
Resident Actions;
Let varMinTime = Num(Peek('minTime', 0, 'Temp2'));
Let varMaxTime = Num(Peek('maxTime', 0, 'Temp2'));
DROP Table Temp2;
TempTimeTable:
LOAD
$(varMinTime) + Iterno()-1 As Num,
Time($(varMinTime) + IterNo() - 1) as TempTime
AutoGenerate 1 While $(varMinTime) + IterNo() -1 <= $(varMaxTime);
MasterTime:
LOAD
time(recno()/96,'hh:mm') as Time2
autogenerate(96)
Resident TempTimeTable
Order By TempTime ASC;
Drop Table TempTimeTable;
I hope it's good that i did that. And sorry if i might not understand yet, still quite newbie
You are using both AUTOGENERATE and RESIDENT in your MasterTime table, which I believe doesn't work.
Then you have reverted back to time conversions which I think won't work, but if this parts works for your, I am out of the business.