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
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!
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!
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;
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!
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;
Yes, you are correct. Thank you for your time and help. Much appreciated
Cheers!
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.
hi there,
upload some sample data
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