topic Re: Creating a Master Time Table in Connectivity & Data Prep

Creating a Master Time Table

Dilie — Thu, 15 Oct 2020 08:45:33 GMT

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

Re: Creating a Master Time Table

Kushal_Chawda — Thu, 15 Oct 2020 10:29:23 GMT

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

Re: Creating a Master Time Table

Dilie — Thu, 15 Oct 2020 11:10:31 GMT

@Kushal_Chawda

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

Re: Creating a Master Time Table

Kushal_Chawda — Thu, 15 Oct 2020 12:13:32 GMT

@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;

Re: Creating a Master Time Table

Dilie — Thu, 15 Oct 2020 12:18:42 GMT

@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!

Re: Creating a Master Time Table

Kushal_Chawda — Thu, 15 Oct 2020 12:22:53 GMT

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

Re: Creating a Master Time Table

Dilie — Thu, 15 Oct 2020 12:28:20 GMT

@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!

Re: Creating a Master Time Table

Kushal_Chawda — Thu, 15 Oct 2020 12:39:05 GMT

@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);

Re: Creating a Master Time Table

Dilie — Thu, 15 Oct 2020 12:50:05 GMT

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!