topic Re: Generate Calendar takes a long time in QlikView

Generate Calendar takes a long time

suzel404 — Wed, 25 Jun 2014 02:55:42 GMT

Hi all,

I want to generate a calendar with my date (Min & Max).

But When I reload I see more than 12 millions of rows to generate a calendar from 25/02/2014 13:30:01 to 25/05/2014 12:11:34.

Can someone help me, please ?

Thanks.

Re: Generate Calendar takes a long time

Anonymous — Wed, 25 Jun 2014 03:34:51 GMT

Looks weird. Just commented the table in your loading script and added "25/02/2014 13:30:01" as Min Date, "25/05/2014 12:11:34" as Max Date, variables quoted with single inverted comma in loading script and it is giving me 89 rows. See below script,

//TEMP:

//LOAD

// num(min(date_create)) AS MinDate,

// num(max(date_create)) AS MaxDate

//RESIDENT Temp_Time;

LET vMinDate = Date#('25/02/2014 13:30:01', 'DD/MM/YYYY hh:mm:ss');//peek('MinDate', 0, 'TEMP');

LET vMaxDate = Date#('25/05/2014 12:11:34', 'DD/MM/YYYY hh:mm:ss');//peek('MaxDate', 0, 'TEMP');

//DROP TABLE Temp_Time;

TempCalendar:

LOAD

date('$(vMinDate)'+IterNo()-1) AS TempDate

AUTOGENERATE (1)

WHILE '$(vMinDate)'+IterNo()-1<= '$(vMaxDate)';

Re: Generate Calendar takes a long time

jagan — Wed, 25 Jun 2014 04:08:23 GMT

Hi,

Check this thread hope it helps you.

Master Calendar Generation Script

Try this script below

CalendarMaster:

LOAD

Date(TempDate) AS TempDate,

Year(TempDate) AS Year,

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

Month(TempDate) As Month,

Day(TempDate) As Day,

Week(TempDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS TempDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

LOAD

Floor(min(date_create)) AS MinDate,

Floor(max(date_create)) AS MaxDate

RESIDENT Temp_Time;

Regards,

jagan.

Re: Generate Calendar takes a long time

kalyandg — Wed, 25 Jun 2014 06:05:14 GMT

hi,

try this

LET varMinDate = Num(MakeDate(2008,8,31));

LET varMaxDate = Floor(MonthEnd(Today()));

LET varToday = Date(Today(), 'MM/DD/YYYY');

LET varYesterday = Date(Today()-1, 'MM/DD/YYYY');

LET varCurrentMonth = Date(Monthstart(Today()-1), 'MMM-YYYY');

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

//Create all days in the range from varMinDate to varMaxDate

TempCalendar:

LOAD

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

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

AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= num(today());

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

//Building the master calendar with most date dimensions

MasterCalendar:

LOAD

TempDate as InvDate,

Day(TempDate) as Day,

If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Num(Month(TempDate))+9,Num(Month(TempDate))-3) as CMonth,

If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Year(TempDate)-1,Year(TempDate))as CYear,

Day(TempDate)&'/'&If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Num(Month(TempDate))+9,Num(Month(TempDate))-3)&'/'&If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Year(TempDate)-1,Year(TempDate)) as InvFinDate,

YearName(TempDate, 0, 4) as FinYearName,

Weekday(TempDate) as WeekDay,

Week(TempDate) as Week,

Month(TempDate) as Month,

Year(TempDate) as Year,

MonthName(TempDate) AS MonthYear,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

'Q' & ceil((If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Num(Month(TempDate))+9,Num(Month(TempDate))-3)) / 3) as FinQuarter,

'Q' & ceil((If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Num(Month(TempDate))+9,Num(Month(TempDate))-3)) / 3) & ' '&If(WildMatch(Month(TempDate),'Jan','Feb','Mar'),Year(TempDate)-1,Year(TempDate)) AS QuarterYear,

date(Today()-1) as Yesterday,

inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag

resident TempCalendar

order by TempDate Asc;

Re: Generate Calendar takes a long time

suzel404 — Wed, 25 Jun 2014 18:52:10 GMT

Thank you,so much