Creating a Master Time Table

[Dilie]

I am trying to create a master Time Table in Qlik Sense. I tried using the same method that I use for my Master Calendar, but without succes...

createdOnTime:
Load
	
    CalDate as createdOn_time,
    Hour(CalDate) as createdOn_time_hour,
    Minute(CalDate) as createdOn_time_minute,
    ;

Load 
	Date(MinTime + IterNo()) as CalDate
While MinTime + IterNo() <= MaxTime;

Load

	Num(Min(FieldValue('createdOn_time', RecNo()))) as MinTime, 
    Num(Max(FieldValue('createdOn_time', RecNo()))) as MaxTime
AutoGenerate FieldValueCount('createdOn_time')
;

createdOn_time is in hh:mm format, I only want to track hours and minutes.

I've read this blog post but it didn't help me forward: https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Time-Table/ba-p/1469392

Could anyone help me with fixing my code or help me with better code for the Master Time Table?

Thanks in advance!

Dilie

[Kushal_Chawda]

@Dilie  how you want to generate time? Do you want to record for each minutes of the day?

[Dilie]

@Kushal_Chawda 

Yes, I want to be able to filter in hours and minutes. Every 15 minute is also ok. 

[Kushal_Chawda]

@Dilie  try below. Assuming createdOn_time is in timestamp format (Date+time) . Below code will create records for every 15 minutes for each day.

Data:
LOAD createdOn_time,
     date(floor(createdOn_time)) as createdOn_Date
FROM Table;

Dates:
LOAD MinDate+IterNo()-1 as Date
While MinDate+IterNo()-1<=MaxDate;
LOAD min(Date) as MinDate,
     max(Date) as MaxDate;
LOAD FieldValue('createdOn_Date',RecNo()) as Date
AutoGenerate FieldValueCount('createdOn_Date');

Join(Dates)
LOAD time(recno()/96,'hh:mm') as Time,
     hour(recno()/96) as Hour,
     Minute(recno()/96) as Minute
autogenerate(96);

Master_time:
LOAD Time,
     Hour,
     Minute,
     timestamp(Date+Time) as createdOn_time
Resident Dates;

DROP Table Dates;

[Dilie]

@Kushal_Chawda 

createdOn_time is in Time format, hh:mm. I already have a createdOn with a master calendar. The only thing I need is a Time table!

[Kushal_Chawda]

@Dilie  can you share example data with expected output?  Because it will give you accurate solution

[Dilie]

@Kushal_Chawda 

This is my data:

 

I want to output it like this:

In the minute it could be for every minute or every 15 min, both is ok for me. As I said in the original post I tried to use the same solution as I use for my master calendar. But as you can see in the screenshots my master timetable does not get filled with data!

[Kushal_Chawda · Accepted solution]

@Dilie  try below. Create separate table and link it with actual table on  createdOn_time

// to generate every 1 minute
Master_time:
LOAD time(recno()/1440,'hh:mm') as createdOn_time,
     hour(recno()/1440) as Hour,
     Minute(recno()/1440)as Minute
autogenerate(1440);

// to generate every 15 minute
Master_time:
LOAD time(recno()/96,'hh:mm') as createdOn_time,
     hour(recno()/96) as Hour,
     Minute(recno()/96)as Minute
autogenerate(96);

. 

[Dilie]

I didn't think about this, for time and hour I can use a static table...  I wanted to generate it based on the times I use in my other table. Thanks for this solution it is working!