Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ebrahimaljefri
New Contributor III

master calendar

hi qlikers

i am loading below script and no error return but when i set YearCalander as filter only i see one record as min value

note that Created field contains values since 2013 until today's date


please help

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

 

Temp3: 

Load 

    min(Created) as minDate, 

               Max(Created)  as maxDate

Resident Data; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp3')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp3')); 

DROP Table Temp3; 

 

TempCalendar: 

LOAD 

                $(varMinDate) + Iterno()-1 As Num,

                Date($(varMinDate) + IterNo() - 1) as TempDate 

                AutoGenerate 12 While $(varMinDate) + IterNo() -1  <= $(varMinDate);

 

 

 

MasterCalendar: 

Load 

               TempDate as Created , 

               week(TempDate) As WeekCalander, 

               Year(TempDate) As YearCalander, 

               Month(TempDate) As MonthCalander, 

               Day(TempDate) As DayCalander, 

               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 QuarterCalander, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

2 Replies
arvind_patil
Valued Contributor III

Re: master calendar

Hi Ibrahibra,

Please change the varibale below:

  AutoGenerate 12 While $(varMinDate) + IterNo() -1  <= $(varMinDate);//Require max variable


Thanks,

Arvind Patil

arvind_patil
Valued Contributor III

Re: master calendar

Use below script:

QuartersMap:

MAPPING LOAD 

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp3:

Load

    min(Created) as minDate,

               Max(Created)  as maxDate

Resident Data;

Let varMinDate = Num(Peek('minDate', 0, 'Temp3'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp3'));

DROP Table Temp3;

TempCalendar:

LOAD

                $(varMinDate) + Iterno()-1 As Num,

                Date($(varMinDate) + IterNo() - 1) as TempDate

                AutoGenerate 12 While $(varMinDate) + IterNo() -1  <= $(varMaxDate );

MasterCalendar:

Load

               TempDate as Created ,

               week(TempDate) As WeekCalander,

               Year(TempDate) As YearCalander,

               Month(TempDate) As MonthCalander,

               Day(TempDate) As DayCalander,

               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 QuarterCalander,

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

               WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;