22 Replies Latest reply: Nov 23, 2015 2:18 AM by Kalmer Kaljuste RSS

    Question about adding time into my master calendar

    Kalmer Kaljuste

      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.

        • Re: Question about adding time into my master calendar
          Prem Kumar Thangallapally

          hi use this

           

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

            • Re: Question about adding time into my master calendar
              Prem Kumar Thangallapally

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

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

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

                • Re: Question about adding time into my master calendar
                  Kalmer Kaljuste

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

                    • Re: Question about adding time into my master calendar
                      Kalmer Kaljuste

                      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

                        • Re: Question about adding time into my master calendar
                          Prem Kumar Thangallapally

                          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

                            • Re: Question about adding time into my master calendar
                              Kalmer Kaljuste

                              Yes, it worked like it should. is it possible to scale both of these to be in a 24 hour scale (so 1 day)
                              But my best regards for you helping me out.

                               

                              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;

                               

                              OH and now i couldn't get these working anymore:

                                             YeartoDate(Date)*-1 as CurYTDFlag,

                                             YeartoDate(Date,-1)*-1 as LastYTDFlag,

                                             inyear(Date, Monthstart($(varMaxDate)),-1) as RC12,

                                             date(monthstart(Date), 'MMM-YYYY') as MonthYear,

                                             ApplyMap('QuartersMap', month(Date), Null()) as Quarter,

                                             Week(weekstart(Date)) & '-' & WeekYear(Date) as WeekYear,

                                             WeekDay(Date) as WeekDay


                              --- i can even load the old calendar to get these working (no problem for me. would be better to have them in the same calendar).
                              Cheers Prem Kumar!

                                • Re: Question about adding time into my master calendar
                                  Kalmer Kaljuste

                                  Sorry ignore this from my last reply. I used Min_30_Bucket and 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;


                                  Thank you!

                                  • Re: Question about adding time into my master calendar
                                    Prem Kumar Thangallapally

                                    Hi kalmer,

                                     

                                    even you can place your old things in same calendar like below

                                    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,

                                              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;

                                     

                                    Load Date,Timestamp(Date&' '&Interval) as FinalDate,

                                                  Date          as       TempDate

                                              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;


                                                 

                                      • Re: Question about adding time into my master calendar
                                        Kalmer Kaljuste

                                        Yes i tried it out, unfortunately it doesn't quite work like that since in TempCalendar i create the TempDate:

                                        TempCalendar: 

                                        LOAD 

                                                    $(varMinDate) + Iterno()-1 As Num, 

                                                      Date($(varMinDate) + IterNo() - 1) as TempDate 

                                                      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

                                        and later in my previous calendar i load TempDate as date so YeartoDate for example can read the right format of Date.

                                        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

                                        And in your code you also define date in Stage1:
                                        Date($(varMinDate)+IterNo()-1) AS Date,


                                        It seemes to go into a conflict and all the (Bold selection from my old calendar) selections give the same result (First data point is 13k for MonthYear and for WeekYear.
                                        Atleast it didn't work. I'll try again, i hope i made a silly mistake . But so far you've done a great job helping me out, cheers!

                                          • Re: Question about adding time into my master calendar
                                            Prem Kumar Thangallapally

                                            hi observe my previous reply carefully , i have created Tempdate field even for preceding Load,check below bold one again,

                                             

                                             

                                             

                                            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,

                                                      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;

                                             

                                            Load Date,

                                                 Timestamp(Date&' '&Interval) as FinalDate,

                                                          Date          as       TempDate ,

                                                      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;

                                • Re: Question about adding time into my master calendar
                                  Kalmer Kaljuste

                                  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?

                                    • Re: Question about adding time into my master calendar
                                      Stefan Wühl

                                      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;

                                        • Re: Question about adding time into my master calendar
                                          Kalmer Kaljuste

                                          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.

                                            • Re: Question about adding time into my master calendar
                                              Kalmer Kaljuste

                                              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

                                              • Re: Question about adding time into my master calendar
                                                Stefan Wühl

                                                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.