topic Re: Re: How to Change this Master Calendar in QlikView

How to Change this Master Calendar

Tue, 29 Apr 2014 05:20:20 GMT

Hi All,

I have a QVW which will generate QVD on weekly basis.

now i need to change the master calendar to generate the QVD in Monthly basis. This is the master calendar we are using

LET tableloadStartTime=0;

SET vTotalRecordCount = 0;

Let NoOfDaysShift =0;

LET tableloadStartTime=Now(1);

if(WeekDay(today())<> 'Sat') and (WeekDay(today())<> 'Sun') Then

let NoOfDaysShift= num(WeekDay(today())*-1)-1;

ENDIF

IF (WeekDay(today())= 'Sun') Then

let NoOfDaysShift= -1;

ENDIF

let vExecDate = date(today()+$(NoOfDaysShift),'MM/DD/YYYY') ;

trace The date of execution is : $(vExecDate);

Let vToday = Today(1); // Custom Run

Let vToday_Num = num(floor(today(1)+$(NoOfDaysShift)));

According to this code QVD will generate on Every Sunday in a week.

Now i need to Change the code to load QVD on sunday in a Monthly once

EX: suppose if i run QVW on 29-4-2014 it generate the QVD based on 27-4-2014.

next if i change the let NoOfDaysShift= num(WeekDay(today())*-1)-1; code to

let NoOfDaysShift= num(WeekDay(today())*-1)+5; it will generate next sunday date which is 03-05-2014

same way i need to change the code . I need to generate the QVD on Monthly basis..

means if i run the QVW today it have to run Only first sunday of every month (i.e 6-4-2014). if i run the QVW tomarrow , also it has to run on 6-4-2014.

In the next month it have to run on 4-5-2014.

How to change the Master Calendar

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 07:09:11 GMT

Please respond any one...I need help Immediately..

Regards

Abhinav

Re: Re: How to Change this Master Calendar

Tue, 29 Apr 2014 08:47:22 GMT

Hi,

Use the below logic to load your script on every month first sunday.

Let vMonth_FirstSunday = Date(WeekEnd(Monthstart(Today())),'DD/MM/YYYY');
Let vToday = Date(Today(),'DD/MM/YYYY');

IF $(vToday) = $(vMonth_FirstSunday) Then
Test:
Load
'First' as First
AutoGenerate 1;

ELSE

Test1:

Load
'Second' as Second
AutoGenerate 1;
ENDIF

Hope this helps you..!

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 09:34:31 GMT

thanks for the reply,

i want to modify the same master calendar to Monthly basis..can u tell me how to do that...

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 09:45:05 GMT

Can you attach your sample app ?

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 10:06:50 GMT

thanks for the reply,

i have attached my qvw ..pls check it.

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 10:42:25 GMT

Use the below script instead of your Main tab script

$(Include=../../../Include/WW/IntegratedReporting/IntegratedReportingGlobal.txt);

LET tableloadStartTime=0;
SET vTotalRecordCount = 0;

LET tableloadStartTime=Now(1);

LET vExecDate = Date(WeekEnd(Monthstart(Today())),'MM/DD/YYYY');

TRACE The date of execution is : $(vExecDate);

Let vToday = Today(1); // Custom Run
Let vToday_Num = num(floor(vExecDate));

Hope this helps you..!

Re: How to Change this Master Calendar

vikasmahajan — Tue, 29 Apr 2014 10:46:01 GMT

Find out min and max date from one or two tables and find attached complete master calender scripts from live software.

TEMP:

LOAD Max(MaxDate) AS MaxDate

FROM

[Source QVDs SIM\DIM_minmax_date.qvd]

(qvd);

/**********************Select Min and Max Date from 'TEMP' Table*************/

LET varMinDate = Num(MakeDate(2008,04,01)); //Num(peek('MinDate', 0, 'TEMP'));

LET varMaxDate = Num(peek('MaxDate', 0, 'TEMP'));

LET vToday = Num(today());

DROP TABLE TEMP;

//LET varMinDate = Num(MakeDate(2007,1,1));

//LET varMaxDate = Floor(YearEnd(AddMonths(Today(), 24)));

//LET vToday = Num(today());

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(varMinDate)+IterNo()-1 AS Num,

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

AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************

MasterCalendar:

LOAD TempDate AS [Posting Date],

TempDate AS %Date,

YearName(TempDate,0,4) AS FinancialYear,

Date(Yearstart(TempDate,0,4)) AS YearStart,

Date(Yearend(TempDate,0,4)) AS YearEnd,

Num(Date(Yearstart(TempDate,0,4),'YYYY')) AS FinancialYearNo,

date(MonthStart(TempDate),'MMM-YY') AS MonthYear,

Month(TempDate) AS MonthName,

If(Num(Month(TempDate))>3,Num(Month(TempDate))-3,Num(Month(TempDate))+9) AS MonthNumber,

MonthStart(TempDate) AS MonthStart,

MonthEnd(TempDate) AS MonthEnd,

Year(TempDate) AS Year,

'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) AS Quarter,

quarterstart(TempDate,0,4) AS QuarterStart,

quarterend(TempDate,0,4) AS QuarterEnd,

quartername(TempDate,0,4) AS QuarterName,

if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate)) AS FiscalYear,

AutoNumber(Month(TempDate) & Year(TempDate)) AS Sequential,

NUM(TempDate) AS DateNum,

monthname(TempDate) AS CalMonthYear,

Day(TempDate) &'-'& Date(TempDate,'MMM') AS DayMon,

num(Year(TempDate)&num(Month(TempDate),'00')) as YEAR_MONTH, // Link between tables for previous month / year selection

num((Year(TempDate)-1)&num(Month(TempDate),'00')) AS YEAR_MONTH_PREVIOUS, // Link between tables for previous month / year selection

num((Year(TempDate)-2)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS

RESIDENT TempCalendar

ORDER BY TempDate Asc;

DROP Table TempCalendar;

Hope this helps you

Vikas

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 10:57:02 GMT

Hi Ravi,

Thanks for the reply its working good...but to check for next month u have not written...like earlier code if we change the noofshiftdays we can check next week or month data by increasing

let NoOfDaysShift= num((WeekDay(MonthStart(today())))*-1)+6;

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 11:01:04 GMT

I think when the time comes, the variable itself will shows the next months first sunday date.

Hope i am not wrong.

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 11:14:21 GMT

you can use like this also

=Date(WeekEnd(Monthstart(AddMonths(Today(),1))),'MM/DD/YYYY') ---> for the next month

=Date(WeekEnd(Monthstart(AddMonths(Today(),-1))),'MM/DD/YYYY') ----> for the previous month

=Date(WeekEnd(Monthstart(AddMonths(Today(),0))),'MM/DD/YYYY') ----> for the current month

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 11:22:06 GMT

Hi Ravi ,

Thanks for the Reply.. Its Working Fine...Please add me as your friend ..

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 11:25:51 GMT

Sorry, I got your point now

try the below script

$(Include=../../../Include/WW/IntegratedReporting/IntegratedReportingGlobal.txt);

LET tableloadStartTime=0;
SET vTotalRecordCount = 0;

LET tableloadStartTime=Now(1);

LET vMonthShift =0;

LET vExecDate = Date(WeekEnd(Monthstart(AddMonths(Today(),$(vMonthShift)))),'MM/DD/YYYY');
TRACE The date of execution is : $(vExecDate);

LET vToday = Today(1); // Custom Run
LET vToday_Num = num(floor(vExecDate));

Re: How to Change this Master Calendar

Tue, 29 Apr 2014 12:03:27 GMT

Hi Ravi,

Thanks for accepting me as friend,

I need one more help.

As per the First QVW which i have attached..

I need to run the QVW in Every sunday in a month ,

after the month end i need to show all the 4 or 5 weeks QVD data whcih is append to every week

if i run the QVW in any day in a month it has to generate the QVD.

Ex: if i run today it will load the QVD based on previous week sunday(i.e 26-4-2014)

like that after month end we need to show all the weekly data in month ..

How can we do this...