Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Master Calendar Creation with out variable creation

Not applicable

Master Calendar Creation with out variable creation

Generally we write script to generate the Master Calendar table based on transaction date in the fact table. The basic idea is calculate the max & min dates and create a table with looping values between min and max dates.

MaxMinDates:

LOAD

     Max(TransactionDate) AS MaxDate,

     Min(TransactionDate) AS MinDate

Resident FactTransactions;

;

Let vMinDate = Peek('MinDate') ;

Let vMaxDate = Peek('MaxDate') ;

DROP Table MaxMinDates;

For i=0 to $(vMaxDate)-$(vMinDate)

MasterCalendar:

LOAD

     Date,

     Month(Date) AS Month,

     Year(Date) AS Year

     WeekName(Date) AS WorkWeek

;

LOAD

     $(vMinDate)+$(i) AS Date

Autogenerate 1

;

Next i

I seen some times this took more time if generating 3 to 4 years date values. So I will replace above logic in load statement with out variables creation. I use the IterNo() function & generate the Loop with in Load statement like below:

MasterCalendar:

LOAD

     Date,

     Month(Date) AS Month,

     Year(Date) AS Year

     WeekName(Date) AS WorkWeek

;

LOAD

     MINDATE + IterNo() -1 AS Date

While IterNo() <= MAXDATE - MINDATE + 1

;

LOAD

     Max(TransactionDate) AS MAXDATE ,

     Min(TransactionDate) AS MINDATE

Resident Transactions ;

This approach is very less expensive when compare to above approach.

I Hope this tip will help to others.

Happy Qlik Scripting.

Comments
kkkumar82
Valued Contributor III

If this really increases the performance then it is really good idea.

Not applicable

You can conduct a simple test. Please check the below script:

// ******* IterNo Approach *******

LET vStart = Now() ;

IterMethod:

LOAD

     IterNo() AS ID

     Today() -  IterNo() +1 AS Date

Autogenerate 1

while iterno() <= 10000

;

LET vEnd = Now() ;

LoadTimes:

LOAD

     'IterNo' AS APPROACH

     $(vStart) AS START_TIME,

     $(vEnd) AS END_TIME

Autogenerate 1

;

DROP Table IterMethod;

SET vStart  = ;

SET vEnd  = ;


// ******* For Loop Approach ******

LET vStart = Now() ;

For i=0 to 10000

IterMethod:

LOAD

     $(i) AS ID

     Today() - $(i) AS Date

Autogenerate 1

;

Next i

LET vEnd = Now() ;

LoadTimes:

LOAD

     'ForLoop' AS APPROACH

     $(vStart) AS START_TIME,

     $(vEnd) AS END_TIME

Autogenerate 1

;

Luminary
Luminary

Hi, I posted this a while ago, it can be used similar to yours, without variables, but its even faster, because it does not use Max/Min.

The Fastest Dynamic Calendar Script (Ever)

Version history
Revision #:
1 of 1
Last update:
‎10-17-2015 07:57 PM
Updated by: