topic Master calender with hours in QlikView

Master calender with hours

ashis — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

I want to create master calendar with hour. My script looks like this

MasterCalender:
LOAD
Date,
Year(Date) AS Year,
'Q' & Ceil(Month(Date) / 3) AS Quarter,

Month(Date) As Month,
Date(MonthStart(Date),'MMM') as Month1,
Date(MonthStart(Date),'MMM-YYYY') as MonthYear,
Year(Date) * 12 + num(Month(Date)) as [Month Counter],
Day(Date) As Day,
WeekDay(Date) AS WeekDays,
Hour(Date) as Hour,
Week(Date) As Week;
Load
Date(MinDate + IterNo() -1 ) AS Date

While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(Start_Time) AS MinDate,
Max(End_Time) AS MaxDate

RESIDENT acb;

Start_Time and End_Time have date format like 'DD-MM-YYYY hh:mm:ss:ttt' .

The hour field in above only returning 22 .

Could you please help.

Re: Master calender with hours

adityaakshaya — Wed, 16 May 2018 07:21:31 GMT

Hi Ashis,

Hour() function only return integer value.

You can use Timestamp(Date,'hh:mm:ss:ttt') and it will give you exactly what you are looking for.

Regards,

Akshaya

PS - If you find response as correct/helpful then mark it Correct/Helpful

Re: Master calender with hours

ashis — Wed, 16 May 2018 11:24:00 GMT

Hi Akshaya,

Thank you for the reply.

Timestamp(Date,'hh:mm:ss:ttt') will format the date field as hour:min:sec am/pm . Actually I am not looking it .

I am looking to generate hours in (24 hours) such as 00, 01, 02 ....23.. in my master calendar.

Re: Master calender with hours

snehamahaveer — Wed, 16 May 2018 11:36:36 GMT

Hello Ashis Sau,

You can do the following to create hour values.

1. Hour(Timestamp#(Date)) - this would return an integer value. Eg 08 hours would be '8'

2. Time(Date,'hh') - this would return the hour with 2 digits Eg 08 hours would be displayed as '08', same can be achieved by Timestamp(Date,'hh')

Hope this helps.

Regards,