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.