Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Master Calendar- how to include hour values?

Hi,

I am new to qklik view and do not manage the date and time stuff very well until now.

I am looking at inflow data, which I load from excel. Since there are missing values I would like to show them grafically, but my original data does not contain the missing values. That's why I am vreating a master calendar, which works fine, but only until months.

When I try to inlcude hours it does not work. I have tried several stuff now from the forum, but I can't make it work. Anybody that can help me?  See below, how I load the data from excel and the skript for the calendar:

For i=0 to 12

let vSheet = 2003+$(i);

RAW:

LOAD

timestamp#(Dato,'DD/MM/YYYY hh:mm:ss') as Dato2,

date(floor(Dato),'DD-MMM-YYYY') as Dato_RL,

Year(Dato) as Year_RL,

month(Dato) as Month_RL,

Day(Dato) as Day_RL,

time(Dato,'hh:mm') as Time_RL,

     [Q tilløb] as Qtilløb_RL,

     [Q bypass]as Qbypass_RL,

     '$(vSheet)' as File_Year_RL

FROM

[..\Tilløb\RL_timeflow.xlsx]

(ooxml, embedded labels, table is [$(vSheet)], filters(

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 2)),

Remove(Row, RowCnd(CellValue, 3, StrCnd(start, '-')))

));

next;

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

 

Temp: 

Load 

               min(Dato_RL) as minDate, 

               max(Dato_RL) as maxDate 

Resident RAW; 

 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

CalendarTemp: 

LOAD 

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

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

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

MasterCalendar: 

Load 

               //TempDate AS Dato_RL,

               TempDate AS Dato_RL, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               Hour(time(TempDate)) as Hour,

               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 CalendarTemp 

Order By TempDate ASC; 

Drop Table CalendarTemp;

Thanks in advance!

1 Solution

Accepted Solutions

Re: Master Calendar- how to include hour values?

Try setting the axis to Continuous. You can do that on the Axes tab


talk is cheap, supply exceeds demand
20 Replies

Re: Master Calendar- how to include hour values?

Have you checked this thread?

Master Calendar with Hour

Alternatively, I suggest looking in here as well:

The Master Time Table

Re: Master Calendar- how to include hour values?

Change the CalendarTemp to include hours:

CalendarTemp:

LOAD

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

               Date($(varMinDate) + IterNo() - 1 +( RecNo()-1)/24) as TempDate

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


talk is cheap, supply exceeds demand
MVP
MVP

Re: Master Calendar- how to include hour values?

Shouldn't that line read:

Date($(varMinDate) + IterNo() - 1 + (RecNo()-1)/24) as TempDate,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Master Calendar- how to include hour values?

Absolutely. Thanks for pointing out that error.


talk is cheap, supply exceeds demand
Not applicable

Re: Master Calendar- how to include hour values?

Hi guys, thanks a lot! My hours are generated BUT is not connected to the data... The year, month and day are and the data changes when I change from 2003-2004 for example...

Is there something in the lower part of the code I have to change?

MasterCalendar: 

Load 

               //TempDate AS Dato_RL,

               TempDate AS Dato_RL, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               Hour(TempDate)AS Hour,

               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 CalendarTemp 

Order By TempDate ASC; 

Drop Table CalendarTemp;

I actually also found out that my 'hour' data in the source data is not uniform...I have data that is 16:00:00, but as well entries like 15:59:59. Might that be the problem? I am just in the process to find out how I convert all XX:59:59 data into the whole hour...

Many thanks in advance!

Re: Master Calendar- how to include hour values?

I have data that is 16:00:00, but as well entries like 15:59:59. Might that be the problem?

Yes, that's the problem. You need to round down your source hour data to whole hours too.


talk is cheap, supply exceeds demand
Not applicable

Re: Master Calendar- how to include hour values?

I actually need to round up, because 15:59:59 should be 16:00....

Not applicable

Re: Master Calendar- how to include hour values?

Got it rounded, but get 00:00 twice.... But I fell over something new of course ;-).

When I round the data and then check it whether it has the same date format as the original data, I can see that if I use 'timestamp' in the list box properties, it returns me a wrong date such as 30/12/1899 and not 2003 etc., while my loaded data is just from 2003-2015.

I have then also checked my master calendar and when I select timestamp in my master calendar then I also get a fx 1905, while when I use the default setting I get nicely 2003, 2004, 2005 etc.. And the data is linked to the Dato_RL data which comes in the right format...

When I then check which timestamp my related tabel (Dato_RL) has, then it goes from 2003-2015... so it has the right data and timestamp. It shows 24 values for each day from 2003-2015 with its corresponding time 00:00:00, 01:00:00, 02:00:00 etc. 

I am a little bit confused I have to admit...

I used this skript to load the dates from the origianl table:

date(floor(Dato),'DD-MMM-YYYY') as Dato_RL,

Time(Floor(Frac(Dato),1/24),'hh') as hours_data,

Time(Round(Frac(Dato),1/24/60),'hh:mm') as Time_data,

I have to make sure that all give the same timestamp right?

Re: Master Calendar- how to include hour values?

it returns me a wrong date such as 30/12/1899

Then you need to check that variable varMinDate gets a correct value. It looks like a string like 11/22/2012 was evaluated as an expression: 11 divided by 22 divided by 2012.


talk is cheap, supply exceeds demand
Community Browser