How to include hour and minute in master calendar

[Anonymous]

Hi guys,

Please find the attached master calendar script i am using, I want to include hour and minute in that,how can i do that. Because I have data in hour and minute and I want to connect with master calendar.

Could you please help me in this

[marcus_sommer]

Mostly it's better to create a master time table: The Master Time Table.

- Marcus

[MK_QSL]

EDITED : with Autogenerate Values

Script is only for Date and DateTimeStamp. You can create Month, Year, Quarter, Week, MonthYear etc using the Date and DateTimeStamp field.

For every minute timestamp calendar use below.

LET vMinDate = Num(MakeDate(2014,1,1));

LET vMaxDate = Num(Today());

CalendarTemp:

LOAD

  Date(Floor(DayStart(TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1))))) as Date,

    TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1)) as DateTimeStamp

AUTOGENERATE 1439

WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

For every hour

LET vMinDate = Num(MakeDate(2014,1,1));

LET vMaxDate = Num(Today());

CalendarTemp:

LOAD

  Date(Floor(DayStart(TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1))))) as Date,

    TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1)) as DateTimeStamp

AUTOGENERATE 23

WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

For every second

LET vMinDate = Num(MakeDate(2014,1,1));

LET vMaxDate = Num(Today());

CalendarTemp:

LOAD

  Date(Floor(DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))))) as Date,

    TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1)) as DateTimeStamp

AUTOGENERATE 86399

WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

[Anonymous]

Thanx Manish for your reply. i could get the hour and minute in the master calendar but when i link with my data it does not correct result,

LET vMinDate = Num(MakeDate(2014,1,1));

LET vMaxDate = Num(Today());

TempCalendar:

LOAD

  Date(Floor(DayStart(TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1))))) as TempDate,

    TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1)) as DateTimeStamp

AUTOGENERATE 1439

WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

MasterCalendar:

LOAD

    Date(TempDate,'M/D/YYYY') AS Date,

    TempDate as CalendarDate,

    TempDate as DisplayDate,

    Day(TempDate) AS OpDay,

    hour(Timestamp(DateTimeStamp)) AS hour,

    minute(Timestamp(DateTimeStamp)) as minute,

    Month(TempDate) AS OpMonth,

    Year(TempDate) AS OpYear,

    'Q' & Ceil(Month(TempDate)/3) AS OpQuarter,

    WeekDay(TempDate) AS OpWeekDay,

    Week(TempDate) AS OpWeek,

    Week(TempDate) & '-' & Year(TempDate) AS OpWeekAndYear,

    Year(TempDate)&Date(TempDate,'MM') as OpYearAndMonth,  

    Month(TempDate) & '-' & Year(TempDate) AS OpMonthAndYear,

    Year(TempDate)&Date(TempDate) as OpMMYYYY,

    Year(TempDate)& 'Q' & Ceil(Month(TempDate)/3) as OpYearQuarter

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

CPU:

LOAD Band_Display,

     CPU%,

     //TIMESTAMP

     date(FLOOR(TIMESTAMP),'M/D/YYYY') as Date

FROM

(ooxml, embedded labels, table is Sheet1);

[jagan]

Hi,

Try this simple script for generating the Hour & Minutes master calendar. 

LET vMinDate = Num(MakeDate(2014,1,1));

LET vMaxDate = Num(Today());

LET vDays = vMaxDate - vMinDate + 2;

CalendarTemp:

LOAD Date(Floor(TimeStamp)) AS Date,

TimeStamp,

Hour(TimeStamp) AS Hour,

Minute(TimeStamp) AS Minute;

LOAD 

    Timestamp($(vMinDate) + (RecNo() - 1)/1440) as TimeStamp

AUTOGENERATE 1440 * $(vDays);

In your script, you have to join Timestamp field to the Master Calendar Timestamp field.

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Hi Mohan,

I have tried same way you told me, but still m not getting any value when i link it.

LET vMinDate = Num(MakeDate(2014,1,1));

LET vMaxDate = Num(Today());

LET vDays = vMaxDate - vMinDate + 2;

TempCalendar:

LOAD Date(Floor(TimeStamp)) AS Date,

TimeStamp,

Hour(TimeStamp) AS Hour,

Minute(TimeStamp) AS Minute;

LOAD

    Timestamp($(vMinDate) + (RecNo() - 1)/1440) as TimeStamp

AUTOGENERATE 1440 * $(vDays);

LOAD

     TIMESTAMP as TimeStamp,

     Server,

     Usage

FROM

(ooxml, embedded labels, table is Sheet2);

I do not know what mistake m doing, but it is not getting linked.

[qlikviewwizard]

Hi Rajashmita,

Please close the threads by selecting Correct Answer and Helpful answers. Thank you.

[jagan]

Hi,

The Timestamp field in both the tables should be in the same format then only this works as expected.  Please check the data in both the tables.

If you attach sample file then it would easier to find the issue.

Regards,

Jagan.

[Anonymous]

m not able to find atach option here..any idea?

[qlikviewwizard]

Hi Please click on Use advanced editor. You will find.