7 Replies Latest reply: May 13, 2014 2:48 AM by Suresh Baabu RSS

    PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE?

    manoj kumar

      PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE?

        • Re: PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE?
          Suresh Baabu

          Hello,

          Please use the script below to create a master calendar for your date column.

          please update DATECOLUMN and SOURCETABLE in the script .

          You might have to re-do the same for other date columns.


          *****************************************


          // Please update your DATECOLUMN (3 places) and Source table name (SOURCETABLE)

          QuartersMap:  

          MAPPING LOAD  

          rowno() as Month, 

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

          AUTOGENERATE (12); 

           

          Temp: 

          Load 

                         min(DATECOLUMN) as minDate, 

                         max(DATECOLUMN) as maxDate 

          Resident SOURCETABLE

           

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

                         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;


          ******************************************************


          Hope it helps!!

          Thanks

            • Re: PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE?
              manoj kumar

              my  field name  is   Posting coloumn  and   table is LFA1  . pls  tell me the exact place where i need to  replace the  these keywords.  pls  i am new to scripting and  qlikview  also

                • Re: PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE?
                  Anand Chouhan

                  Try this master calendar

                   

                   

                  //Calendar:

                  LET vDateMin = Num(MakeDate(2011,01,01));

                  Let vDateMax = Floor(Today());

                   

                   

                  TempCalendar:

                  LOAD

                  date($(vDateMin)+IterNo()-1) AS Datefield,

                  date($(vDateMin)+IterNo()-1) AS TempDate

                  AUTOGENERATE (1)

                  WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                   

                   

                  Calendar:

                  LOAD

                  TempDate as PostingDate, //conect this column with your table field with table LFA1

                  Date(TempDate) AS ActionTime,

                   

                  // Standard Date Objects

                  Day(TempDate) AS CalendarDayOfMonth,

                  WeekDay(TempDate) AS CalendarDayName,

                  Week(TempDate) AS CalendarWeekOfYear,

                  Month(TempDate) AS CalendarMonthName,

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

                  Year(TempDate) AS CalendarYr,

                   

                  // Calendar Date Names

                  WeekName(TempDate) as CalendarWeekNumberAndYear,

                  MonthName(TempDate) as CalendarMonthAndYear,

                  QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

                   

                  // Start Dates

                  DayStart(TempDate) as CalendarDayStart,

                  WeekStart(TempDate) as CalendarWeekStart,

                  MonthStart(TempDate) as CalendarMonthStart,

                  QuarterStart(TempDate) as CalendarQuarterStart,

                  YearStart(TempDate) as CalendarYearStart,

                   

                  // End Dates

                  DayEnd(TempDate) as CalendarDayEnd,

                  WeekEnd(TempDate) as CalendarWeekEnd,

                  MonthEnd(TempDate) as CalendarMonthEnd,

                  QuarterEnd(TempDate) as CalendarQuarterEnd,

                  YearEnd(TempDate) as CalendarYearEnd,

                   

                  // Combo Date Examples

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

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

                  //'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays,

                   

                  //Relative Dates

                  Today() - TempDate as DaysAgo,

                  (WeekStart(Today()) - WeekStart(TempDate))/7 as WeeksAgo,

                  12*(Year(Today())-Year(TempDate)) + Month(Today()) - Month(TempDate) as MonthsAgo,

                  MonthName(today()) as ThisMonth,

                  MonthName(AddMonths(today(),-1)) as PrevMonth

                   

                   

                   

                  RESIDENT TempCalendar ORDER BY TempDate ASC;

                   

                  DROP TABLE TempCalendar;

                  • Re: PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE?
                    Suresh Baabu

                    Copy the script to a Notepad -> CTRL+H  ->       

                    Find for ‘DATECOLUMN’ Replace with ‘Posting’

                    Find for ‘SOURCETABLE’ Replace with ‘LFA1’

                     

                • Re: PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE?
                  Amit Saini

                  Hi Manoj,

                   

                  Script for Master Calender:

                   

                  Let StartCalendar = num(MakeDate(year(today())-1, '01', '01'));

                  //Let EndCalendar = num(YearEnd(today()));

                  //---------------------------------------------------------------------

                   

                  //Autogenerate Calendar with start and end Date

                  Cal:

                  LOAD Date ($(StartCalendar) + RecNo()-1) as CalDate

                  AutoGenerate ((today()-1) - $(StartCalendar)+1 );

                   

                  //Left Join with no keys

                  left Join (Cal)

                  Load date(CalDate, 'DD-MMM-YYYY') as DateID

                  Resident Cal;

                   

                   

                   

                  //CAL:

                  //Load

                  //    DateID,

                  ////    Date(SALES_DATE, 'DD/MM/YYYY') as SALES_DATE_1,

                  //    Month(DateID) as SALES_MONTH,

                  //    Year(DateID) as SALES_YEAR,

                  //    MonthName(DateID) as SALES_MONTHNAME,

                  //    'Q' & Ceil(Month(DateID)/3) AS SALES_QUARTER,

                  //    DayNumberOfYear(DateID) as DayNumberOfYear,

                  //    Day(DateID) as DayNumberOfMonth,

                  //    DayNumberOfQuarter(DateID) as DayNumberOfQuarter,

                  //    num(Month(DateID)) as MonthNum

                  //    Resident Cal;

                  //drop Table Cal;

                   

                   

                   

                  You can modify this script according to your Date filed.

                   

                   

                  Thanks,
                  AS

                  • Re: PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE?

                    Hi,

                     

                    tmpCalender:

                    LOAD

                    min(Date) as MinDate,

                    max(Date) as MaxDate

                    Resident tmpDate;

                     

                    Quarter_Map:

                    Mapping

                    LOAD * INLINE [

                    Month, Quarter

                    Jan, Q2

                    Feb, Q2

                    Mar, Q2

                    Apr, Q3

                    May, Q3

                    Jun, Q3

                    Jul, Q4

                    Aug, Q4

                    Sep, Q4

                    Oct, Q1

                    Nov, Q1

                    Dec, Q1

                    ];

                     

                    /////////////////////////////////////

                    LET vDateMin = Num(Peek('MinDate', 0, 'tmpCalender'));

                    LET vDateMax = Num(Peek('MaxDate', 0, 'tmpCalender'));

                    LET vDateToday = Num(Today());

                     

                    TempCalendar1:

                    LOAD

                    $(vDateMin) + RowNo() - 1 AS Date_Key,

                    Date($(vDateMin) + RowNo() - 1) AS Date

                    AUTOGENERATE 1

                    WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                    Master_Calendar:

                    load Date_Key

                    ,Date as Date

                    ,Date as Receipt.ReceiptDate

                    ,Date as CalendarDate

                    ,Day(Date) as CalendarDay

                    ,Week(Date) as CalendarWeek

                    ,WeekName(Date) as CalendarWeekName

                    ,month(Date) as CalendarMonth

                    ,MonthName(Date) as CalendarMonthName

                    ,MonthName(Date) as CalendarPeriod

                    ,Quartername(Date) as CalendarQuarterName

                    ,ApplyMap('Quarter_Map',num#(month(Date)))as CalendarQuarter

                    ,year(Date) as CalendarYear

                    ,WeekDay(Date) as CalendarWeekDay

                    ,text(weekday(Date)) as Week_Day1

                    ,InYearToDate(Date,$(vDateToday),0)* -1 as CurYTDFlag

                    ,inyeartodate(Date, $(vDateToday), -1) * -1 as LastYTDFlag

                    Resident TempCalendar1;

                     

                    DROP Table TempCalendar1;

                    DROP Table tmpCalender;

                    DROP Table tmpDate;

                     

                    Hope this can help you.

                     

                    thanks,

                    Raja.