calender

[qlikapple]

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

[Chanty4u]

use this

1. QuartersMap: 
2. MAPPING LOAD  
3. rowno() as Month, 
4. 'Q' & Ceil (rowno()/3) as Quarter 
5. AUTOGENERATE (12); 
6.  
7. Temp: 
8. Load 
9.                min(OrderDate) as minDate, 
10.                max(OrderDate) as maxDate 
11. Resident Orders; 
12.  
13. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
15. DROP Table Temp; 
16.  
17. TempCalendar: 
18. LOAD 
19.                $(varMinDate) + Iterno()-1 As Num, 
20.                Date($(varMinDate) + IterNo() - 1) as TempDate 
21.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
22.  
23. MasterCalendar: 
24. Load 
25.                TempDate AS OrderDate, 
26.                week(TempDate) As Week, 
27.                Year(TempDate) As Year, 
28.                Month(TempDate) As Month, 
29.                Day(TempDate) As Day, 
30.                YeartoDate(TempDate)*-1 as CurYTDFlag, 
31.                YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
32.                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
33.                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
34.                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
35.                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
36.                WeekDay(TempDate) as WeekDay 
37. Resident TempCalendar 
38. Order By TempDate ASC; 
39. Drop Table TempCalendar; 

[Chanty4u]

for Hour

Hour(Timestamp(DateField))  as Hour

[qlikapple]

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

[sunny_talwar]

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

[Anonymous]

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

Try solve in the script!

[qlikapple]

HI My source is .xlsx ,