21 Replies Latest reply: Jul 2, 2013 6:42 AM by Koushik Banerjee RSS

    Master Calendar with MinDate and MaxDate from table

      Im attempting to create a master calendar, where

      • MinDate = the earliest date from the Column 'REPORT_DATE' in the Table 'TICKET' and
      • MaxDate = the latest date from the Column 'REPORT_DATE' in the Table 'TICKET'

       

      Each entry in the Column 'REPORT_DATE' is formated like this"DD-MM-YYYY HH:MM"

       

      I have tried the following

      Min_Max:
      LOAD
                Date(Floor(Min(Timestamp#(REPORT_DATE, 'DD-MM-YYYY hh:mm')),'DD-MM-YYYY')) as MinDate,
                Date(Floor(Max(Timestamp#(REPORT_DATE, 'DD-MM-YYYY hh:mm')),'DD-MM-YYYY')) as MaxDate
      Resident TICKET;
      

       

      and then created the vaiables and afterwards dropping the above table as well as creating a temp table

      LET vMinDate = Peek('MinDate', 0, 'Min_Max');
      LET vMaxDate = Peek('MaxDate', 0, 'Min_Max');
      
      DROP Table Min_Max;
      
      Calendar_tmp:
      LOAD
                RowNo()          + $(vMinDate) - 1 as TempDate
      AutoGenerate $(vMaxDate) - $(vMinDate) + 1;
      

       

      Here after creating the master calendar

      MasterCalendar:
      LOAD
                Date(TempDate) as Date,
                Year(TempDate) as Year,
                Month(TempDate) as Month,
                Day(TempDate) as Day,
                Week(TempDate) as Week,
                Weekday(TempDate) as Weekday,
                'Q' & Ceil(Month(TempDate)/3) as Quarter,
                Date(MonthStart(TempDate), 'YYYY-MM') as YearMonth,
                Year(TempDate) & '-' & Week(TempDate) as YearWeek
      RESIDENT Calendar_tmp;
      DROP Table Calendar_tmp;
      

       

      Im still very new to QlikView, so my attempt has been assembled from various sources and what I could find on the forum, so if you can provide comments with explanation to what Im doing wrong and what I should do instead, it would help me a lot. Thank you very much for your time and help

       

      Sources

        • Re: Master Calendar with MinDate and MaxDate from table
          Kaushik Solanki

          Hi,

           

               I couldnt understand where is your problem.

           

               Kindly explain with example.

           

          Regards,

          Kaushik Solanki

            • Re: Master Calendar with MinDate and MaxDate from table

              Sorry for not being clear.

               

              My problem is that the above code does not work. Im trying to create a master calendar with the REPORT_DATE as earliest and latest date, but every time I reload Im given the following error:

               

              Script line error:

              Calendar_tmp:

              LOAD

                        RowNo()          +  - 1 as TempDate

              AutoGenerate  -  + 1

               

              Table not found

              MasterCalendar:

              LOAD

                        Date(TempDate) as Date,

                        Year(TempDate) as Year,

                        Month(TempDate) as Month,

                        Day(TempDate) as Day,

                        Week(TempDate) as Week,

                        Weekday(TempDate) as Weekday,

                        'Q' & Ceil(Month(TempDate)/3) as Quarter,

                        Date(MonthStart(TempDate), 'YYYY-MM') as YearMonth,

                        Year(TempDate) & '-' & Week(TempDate) as YearWeek

              RESIDENT Calendar_tmp

               

              Table not found

              DROP TABLES statement

            • Re: Master Calendar with MinDate and MaxDate from table
              Mohit Sharma

              to get minimum timestamp try this

              Timestamp(timesatmp#(TempDate, 'DD-MM-YYYY 'hh:mm:ss'),'hh:mm:ss') as TimeStamp

              or in yout above code

              try to use time format in small letter

              hope it helps

              • Re: Master Calendar with MinDate and MaxDate from table
                Koushik Banerjee

                Hello Philip,

                Please try with the following calendar application  where you can create Calendar Master :

                 

                Step 1)---------------------------------------------------------------------------------------------------------------------------------------

                 

                Caltemp:

                LOAD

                num(min(%Date)) as MinnDate,

                num(max(%Date)) as MaxxDate,

                max(%TranDate,1) As LastTranDate

                resident TableName;

                 

                 

                let vLastTransactionDate = date(peek('LastTranDate',0,'Caltemp'));

                 

                LET vMinnDate = peek('MinnDate',0,'Caltemp');

                LET vMaxxDate = peek('MaxxDate',0,'Caltemp');

                 

                DROP Table Caltemp;

                 

                DateIsland:

                 

                LOAD

                    date($(vMinnDate) + RowNo()-1) as D,

                    Year($(vMinnDate) + RowNo()-1) as Y,

                    Month($(vMinnDate) + RowNo()-1) as M,

                    date(MonthStart($(vMinnDate) + RowNo() -1),'MMM-YYYY') as MY,

                    week($(vMinnDate) + RowNo() -1) & '-' & Year($(vMinnDate)+RowNo()-1) AS WY,

                   

                    YearName($(vMinnDate) + rowno() - 1,0,4) As FullFinYear,

                    date(yearstart($(vMinnDate) + rowno() - 1,0,4),'YYYY') AS YearFin,

                    '01/' & date((monthsstart(1,date($(vMinnDate) + rowno() - 1),0,4)),'MM') & '/' & date(yearstart($(vMinnDate) + rowno() - 1,0,4),'YYYY') As FinMonYear,

                   

                    If(month(date($(vMinnDate) + RowNo()-1)) <4,Floor(month(date($(vMinnDate) + RowNo()-1))+9),Floor(month(date($(vMinnDate) + RowNo()-1))-3)) As MO

                   

                    AutoGenerate

                   

                    vMaxxDate - vMinnDate + 1 ;

                 

                Step 2) -----------------------------------------------------------------------------------------------------------------------------------------------

                 

                Calendar:

                Load

                    D as %TranDate,

                    D as TranDate,

                    D As InvoiceDate,

                    date(D,'DD MMM YYYY') As Date,

                   

                    M As Month,

                    MO As MonthOrder,

                   

                    month(date('01/' & If(month(D) <4,Floor(month(D)+9),Floor(month(D)-3)) & '/' & YearFin))  AS FMonth,

                   

                    week(D) As Week,

                    (Week(D)-13) As FWeek,

                    WeekDay(D) As Weekday,

                    WY As WeekYear,

                    day(D) As Day,

                    date(D,'MM/DD') As DateMMDD,

                   

                    date(FinMonYear,'MMM-YYYY') As MonthYear,

                    mid(FullFinYear,3,3) & right(FullFinYear,2) As SHYear,

                 

                    mid((Left(FullFinYear,4)+1),3,3) &'-'& right((right(FullFinYear,4)+1),2) As NXYear,

                   

                    mid((Left(FullFinYear,4)-1),3,3) &'-'& right((right(FullFinYear,4)-1),2) As LYYear,

                   

                    FullFinYear As FinYear,

                    YearFin As Year,

                   

                    'Q' & if(month(D)<4,4,floor(month(D)/3.1)) As Quarter,

                    dual('Q' & if(month(D)<4,4,floor(month(D)/3.1)) & ' ' & YearFin,QuarterStart(D)) As QuarterYear

                   

                Resident DateIsland Order By Y,MO;

                 

                Drop table DateIsland;

                 

                Perhaps it may full fill your problem.

                Thank you.