17 Replies Latest reply: Jul 26, 2015 8:13 AM by Sunny Talwar RSS

    Master Calander

    abc a

      Hi,

      How to create master calander for it,

        • Re: Master Calander
          kushal chawda

          Please see the attached.

          • Re: Master Calander
            Sunny Talwar

            So I did testing with 100 M records and it seems that Min() and Max() method is better than First and it seems to be because of the order by statement.

             

            Method 1 Script:

            Fact:

            LOAD Date(40179 + Ceil(Rand() * 2190)) as Date

            AutoGenerate 100000000;

             

            QuartersMap:

            MAPPING LOAD

            rowno() as Month,

            'Q' & Ceil (rowno()/3) as Quarter

            AUTOGENERATE (12);

             

            MinMaxDate:

            LOAD Min(Date) as MinDate,

                      Max(Date) as MaxDate

            Resident Fact;

             

            LET varMaxDate= Peek('MaxDate',0,'MaxDate');

            LET varMinDate= Peek('MinDate',0,'MinDate');

             

            TempCalendar:

            LOAD $(varMinDate) + Iterno()-1 As Num,

                Date($(varMinDate) + IterNo() - 1) as TempDate

                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

             

            MasterCalendar:

            Load TempDate AS Date,

                Week(TempDate) As Week,

                Year(TempDate) As Year,

                Month(TempDate) As Month,

                Day(TempDate) As Day,

                YeartoDate(TempDate)*-1 as CurYTDFlag,

                YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

                InYear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

                Date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                WeekDay(TempDate) as WeekDay

            Resident TempCalendar

            Order By TempDate ASC;

            Drop Table TempCalendar;

             

            Logfile for Method 1: 20 Seconds to load the Min & Max Values


            Capture.PNG


            Method 2 Script:

            Fact:

            LOAD Date(40179 + Ceil(Rand() * 2190)) as Date

            AutoGenerate 100000000;

             

            QuartersMap:

            MAPPING LOAD

            rowno() as Month,

            'Q' & Ceil (rowno()/3) as Quarter

            AUTOGENERATE (12);

             

            MinDate:

            First 1 LOAD Num(Date) as MinDate

            Resident Fact

            Order by Date asc;

             

            MaxDate:

            First 1 LOAD Num(Date) as MaxDate

            Resident Fact

            Order by Date desc;

             

            LET varMaxDate= Peek('MaxDate',0,'MaxDate');

            LET varMinDate= Peek('MinDate',0,'MinDate');

             

            TempCalendar:

            LOAD $(varMinDate) + Iterno()-1 As Num,

                Date($(varMinDate) + IterNo() - 1) as TempDate

                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

             

            MasterCalendar:

            Load TempDate AS Date,

                Week(TempDate) As Week,

                Year(TempDate) As Year,

                Month(TempDate) As Month,

                Day(TempDate) As Day,

                YeartoDate(TempDate)*-1 as CurYTDFlag,

                YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

                InYear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

                Date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                WeekDay(TempDate) as WeekDay

            Resident TempCalendar

            Order By TempDate ASC;

            Drop Table TempCalendar;


            Logfile for Method 2: Took 2 minutes and 58 Seconds to load the Min & Max Values

             

            Capture.PNG

             

            There might be other variables which cause Method 2 to be more efficient, but purely looking at the Date Column results, it seems Min and Max is a better option.

             

            Best,

            Sunny

            • Re: Master Calander
              neetha P

              hi,

               

              See Below script :

               

              Order:

              LOAD [vTime >= 0 and vTime <= 3],

                   [Order ID],

                   [Order Date],

                   

                   [Order Priority],

                   [Order Quantity],

                   Sales,

                   Discount,

                   [Ship Mode],

                   Profit,

                   [Unit Price],

                   [Shipping Cost],

                   [Customer Name],

                   Province,

                   Region,

                   [Customer Segment],

                   [Product Category],

                   [Product Sub-Category],

                   [Product Name],

                   [Product Container],

                   [Product Base Margin],

                

                   [Ship Date]

              FROM

              [C:\qlikview\Sample Sales Data.xls]

              (biff, embedded labels, table is Orders$);

               

               

              /*************** DateBridge Table **************/

               

              Date:

                   Load [Order ID], [Order Date] as Date, 'Ordered' as DateType

                        Resident Order;

                   Load [Order ID],[Ship Date] as Date, 'Shipped' as DateType

                        Resident Order;

                 

               

               

              MinMax:

              LOAD

                Min(Date) as MinDate,

                Max(Date) as MaxDate

              RESIDENT  Date;

               

              LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

              LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

              LET vToday = $(vMaxDate);

               

               

              /*************** Temporary Calendar **************/

               

              TempCalendar:

              LOAD

                date($(vMinDate) + rowno() - 1) AS TempDate

              AUTOGENERATE

                $(vMaxDate) - $(vMinDate) + 1;

              DROP TABLE MinMax;

               

              /*************** Master Calendar ****************/

               

              MasterCalendar:

              LOAD

                TempDate AS Date,

                Week(TempDate) AS Week,

                Year(TempDate) AS Year,

                Month(TempDate) AS Month,

                Day(TempDate) AS Day,

                Weekday(TempDate) AS WeekDay,

                'Q' & ceil(month(TempDate) / 3) AS Quarter,

                Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

                Week(TempDate)&'-'&Year(TempDate) AS WeekYear

              RESIDENT TempCalendar

              ORDER BY TempDate ASC;

              DROP TABLE TempCalendar;