topic Re: Need Help in QlikView

Need Help

Tue, 13 Oct 2015 08:09:09 GMT

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..

Re: Need Help

MK_QSL — Tue, 13 Oct 2015 08:16:40 GMT

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 !

Re: Need Help

hic — Tue, 13 Oct 2015 08:25:21 GMT

... 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

Re: Need Help

qlikviewwizard — Tue, 13 Oct 2015 08:35:16 GMT

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.

Re: Need Help

hic — Tue, 13 Oct 2015 08:46:47 GMT

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

Re: Need Help

Gabriel — Tue, 13 Oct 2015 09:47:50 GMT

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

Re: Need Help

SreeniJD — Tue, 13 Oct 2015 09:55:16 GMT

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

Re: Need Help

Tue, 13 Oct 2015 14:59:42 GMT

Thanks EveryOne..

Very Helpful....

Re: Need Help

SreeniJD — Tue, 13 Oct 2015 15:04:03 GMT

Great! Please mark the suggestions as helpful or correct.

Sreeni