topic calender in QlikView

calender

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

HI all,

I have a datefieldlike this , can any one please provide the script for master calender like year, month, day, and hour

and also in hour s ( from 0 to 23 Hours)

Please help me

Re: calender

Chanty4u — Tue, 27 Mar 2018 08:40:36 GMT

use this

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
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 TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Re: calender

Chanty4u — Tue, 27 Mar 2018 08:41:55 GMT

for Hour

Hour(Timestamp(DateField)) as Hour

Re: calender

qlikapple — Tue, 27 Mar 2018 11:39:47 GMT

Hi i have done this ,but is coming like 1 to 12 ( which is in 12 hour format), i want to get the hour format as 0 to 23 (or) 1-24 hour format , that too i need master clander .

The actaula date format which i am getting from the source is like this

Re: calender

sunny_talwar — Tue, 27 Mar 2018 11:46:55 GMT

for 24 hour format... you need to use this for your timestamp format in the environmental variable

SET TimestampFormat = 'M/D/YYYY h:mm:ss';

instead of

SET TimestampFormat = 'M/D/YYYY h:mm:ss TT';

Re: calender

Anonymous — Tue, 27 Mar 2018 11:57:17 GMT

What is your data source? XLS? SQLServer? Oracle?

Try solve in the script!

Re: calender

qlikapple — Wed, 28 Mar 2018 11:57:45 GMT

HI My source is .xlsx ,