Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

kalmer_kaljuste
Contributor

Re: Question about adding time into my master calendar

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_kaljuste
Contributor

Re: Question about adding time into my master calendar

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
Valued Contributor II

Re: Question about adding time into my master calendar

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_kaljuste
Contributor

Re: Question about adding time into my master calendar

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
Valued Contributor II

Re: Question about adding time into my master calendar

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_kaljuste
Contributor

Re: Question about adding time into my master calendar

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

kalmer_kaljuste
Contributor

Re: Question about adding time into my master calendar

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

Highlighted
preminqlik
Valued Contributor II

Re: Question about adding time into my master calendar

hi there,

upload some sample data

kalmer_kaljuste
Contributor

Re: Question about adding time into my master calendar

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

Community Browser