Question about adding time into my master calendar

[Kalmer]

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.

[preminqlik]

hi use this

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

[preminqlik]

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

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

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

[Kalmer]

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

[Kalmer]

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]

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?

[swuehl]

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]

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]

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

[swuehl]

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.