Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Kalmer
Partner - Creator
Partner - Creator

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.

20 Replies
preminqlik
Specialist II
Specialist II

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

Kalmer
Partner - Creator
Partner - Creator
Author

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!

Kalmer
Partner - Creator
Partner - Creator
Author

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!

preminqlik
Specialist II
Specialist II

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;


             

Kalmer
Partner - Creator
Partner - Creator
Author

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!

preminqlik
Specialist II
Specialist II

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;

Kalmer
Partner - Creator
Partner - Creator
Author

Yes, you are correct. Thank you for your time and help. Much appreciated
Cheers!

Kalmer
Partner - Creator
Partner - Creator
Author

Oh i hate to be a burden since i found a bug in it i guess (well i'm pretty sure that you know that everything is counted from 0 so if we make for example 7 days (mon-sun). and i did saw you make -1.
But there still is a problem... I will attach also a picture to the reply.

Problem: Some of my values in timetable have an extra measure of time "-" (in all time measures, hours, minutes, seconds, Year, Month, MonthYear and so on).
Can you help me fix that problem and we could close this thread.
And these millions rows are only 1% of the whole database, else i would upload a example. I also tried to load data with First X-amount SQL SELECT tablename (Qlik reads and finds no errors in my app, but dosent count the First X-amount.
One+problem+still+up.png

preminqlik
Specialist II
Specialist II

hi there,

upload some sample data

Kalmer
Partner - Creator
Partner - Creator
Author

Ok i managed to take 1 month of data. Please have a look, no button in the txt redactor to add files v.v so i used an external location. It's 12 mb and safe.
The example file