Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kalmer_kaljuste
Contributor

Question about adding time into my master calendar

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:

pask.png

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.

1 Solution

Accepted Solutions
preminqlik
Valued Contributor II

Re: Question about adding time into my master calendar

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

20 Replies
preminqlik
Valued Contributor II

Re: Question about adding time into my master calendar

hi use this

time(frac(<UnixTimeStamp>/ 86400 + 25569)) as Time

preminqlik
Valued Contributor II

Re: Question about adding time into my master calendar

timestamp(<UnixTimeStamp>/ 86400 + 25569) as TS,

date(floor(<UnixTimeStamp>/ 86400 + 25569)) as Date,

time(frac(<UnixTimeStamp>/ 86400 + 25569)) as Time,

kalmer_kaljuste
Contributor

Re: Question about adding time into my master calendar

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).

Highlighted
kalmer_kaljuste
Contributor

Re: Question about adding time into my master calendar

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

kalmer_kaljuste
Contributor

Re: Question about adding time into my master calendar

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:Isnt working quite right.png

Or if im doing it completely wrong, then would someone give me tips how to correct it all?

MVP
MVP

Re: Question about adding time into my master calendar

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;

kalmer_kaljuste
Contributor

Re: Question about adding time into my master calendar

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.

kalmer_kaljuste
Contributor

Re: Question about adding time into my master calendar

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

MVP
MVP

Re: Question about adding time into my master calendar

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.

Community Browser