topic Re: PLS SHARE SCRIPT FOR MASTER CALENDAR HAVING A FIELD POSTING DATE? in QlikView

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

Tue, 13 May 2014 05:52:30 GMT

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

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

sureshbaabu — Tue, 13 May 2014 06:03:52 GMT

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?

amit_saini — Tue, 13 May 2014 06:09:38 GMT

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?

Tue, 13 May 2014 06:16:01 GMT

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?

its_anandrjs — Tue, 13 May 2014 06:21:43 GMT

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?

Tue, 13 May 2014 06:22:20 GMT

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.

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

Tue, 13 May 2014 06:24:16 GMT

i am also from meeru ekkada undedhi brother

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

sureshbaabu — Tue, 13 May 2014 06:48:03 GMT

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

Find for ‘DATECOLUMN’ Replace with ‘Posting’

Find for ‘SOURCETABLE’ Replace with ‘LFA1’