Need Help

[#1]

Hi All,

I have Millions of records in my data. i have created master calendar.

Performence very slow..

I have made like this

let vMinDate = num(Peek('date',0,'Test'));

let vMaxDate = num(Peek('date',-1,'Test'));

//LET vToday = vMaxDate;

TempDate:

Load date($(vMinDate)+RecNo()-1) as TempDate

AutoGenerate $(vMaxDate) -$(vMinDate);

TimeDim:

LOAD

TempDate as date,

Month(TempDate) as Month,

Day(TempDate) as Day,

DayName(TempDate) as DayName,

num(Month(TempDate)) as MonthID,

'Q'&Ceil(num(Month(TempDate))/3) as Quarter

Resident TempDate;

is there any another solution?????

How to perfermence boost??

Thanks in Advance..

[MK_QSL]

The Fastest Dynamic Calendar Script (Ever)

The Master Calendar

Understanding the Master Calendar - Qlik Sense and QlikView - YouTube

Better Calendar Scripts | Qlikview Cookbook

The last one is the one which you are looking for !

[hic]

... which would be

Dates:
Load
Date,
  Year(Date) as Year,
  Dual('Q' & Ceil(Num(Month(Date))/3), Ceil(Num(Month(Date))/3)) as Quarter,

  Month(Date) as month,
  Date(MonthStart(Date),'YYYY MMM') as Month,
  Day(Date) as Day;
Load

  Date(MinDate + IterNo()) as Date
  While MinDate + IterNo() <= MaxDate;
Load
  Min(FieldValue('Date', RecNo()))-1 as MinDate,
  Max(FieldValue('Date', RecNo())) as MaxDate
  Autogenerate FieldValueCount('Date');

HIC

[qlikviewwizard]

Hi hic ,

How vMinDate and vMaxDate will pass the values to the script.

I did not understand the two variables.

let vMinDate = num(Peek('date',0,'Test'));

let vMaxDate = num(Peek('date',-1,'Test'));

Please explain.

[hic]

In Nagaraju's script, these two variables will be assigned the dates of the first and last record. Which isn't necessarily the first and last date...

My suggested script doesn't use variables. Instead it fetches these values using max and min of FieldValue().

HIC

[Gabriel]

Hi,

There are been excellent suggestions before his, I will just like to add one point, that if you're loading millions of data daily. The date field must have a constant Min date especially if you're loading historical data.

So I will say type exactly what the Min Date is, say

LET vMinDate = '02/03/1999';

Then you can  Say

LET vMaxDate = Date(Now()); // This depends on your data

But the Min date if you can ad code it and it stops scanning millions of rows for Min Date.

Hope this helps to improve the performance

[SreeniJD]

Hi Raju

Gabsus04

suggestion is very good. What you need to do is convince your leads/offshore to agree for a min date to be hard coded and take the current or last refresh date as max date. This will definitely solve the major issue with Performance of reloading master calendar every day.

Regards,

Sreeni

[#8]

Thanks EveryOne..

Very Helpful....

[SreeniJD]

Great! Please mark the suggestions as helpful or correct.

Sreeni