Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
The Fastest Dynamic Calendar Script (Ever)
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 !
... 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
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.
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
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
Hi Raju
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
Thanks EveryOne..
Very Helpful....
Great! Please mark the suggestions as helpful or correct.
Sreeni