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

[#1]

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

[sureshbaabu]

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

[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

[#4]

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

[its_anandrjs]

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;

[#6]

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.

[#7]

i am also from meeru  ekkada undedhi  brother

[sureshbaabu]

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

Find for ‘DATECOLUMN’ Replace with ‘Posting’

Find for ‘SOURCETABLE’ Replace with ‘LFA1’