5 Replies Latest reply: Nov 29, 2012 6:53 AM by Madu Raju RSS

    Calendar script

    Madu Raju

      [Calendar]:

      LOAD *

      ,dual('Q' & "Quarter Only" & ' ' & "Year Only",quarterstart("Date")) as "Quarter"

      ;

      LOAD *

      ,"Month Only"/3 as "Quarter Only_testing","Month Only" as "Quarter Only_testing1",

      ceil("Month Only"/3) as "Quarter Only_test",

      'Q'&ceil("Month Only"/3) as "Quarter Only"

      ;

      LOAD *

      ,date(monthstart("Date"),'MMM YYYY') as "Month"

      ,date(yearstart("Date"),'YYYY') as "Year"

      ,month("Date") as "Month Only"

      ,year("Date") as "Year Only"

      ;

      LOAD date(today() + 10000 - recno()) as Date

      AUTOGENERATE 2000

      ;

       

       

       

      Can you pls any one explain me about this script.

       

      Thanks,

        • Re: Calendar script
          Mohit Sharma

          u will read about this either through in refrence manual or in qlikview help.. and it must be clear from that.

          hope this help....

            • Re: Calendar script
              Madu Raju

              I understand, but I didn't get this line

               

              LOAD date(today() + 10000 - recno()) as Date

              AUTOGENERATE 2000;

               

              Can you pls explain.

               

              Thanks,

                • Re: Calendar script
                  Peter Cammaert

                  That line generates 2000 records numbered from 1 to 2000. RecNo() returns the number of the record that is currently being generated. This value is subtracted from today()+10000 e.g. 10000 days in the future.

                   

                  So the first record has a date of today() + 9999 days, the second today() + 9998 days etc.

                   

                  Peter

              • Re: Calendar script
                Peter Cammaert

                It's usually better to start a discussion with a specific question, instead of "please explain QlikView scripting".

                 

                In short:

                 

                3 PRECEDING LOADs and one LOAD AUTOGENERATE to create single table CALENDAR

                Each PRECEDING LOAD takes its data from the next line (by way of RESIDENT tables)

                Read the script in reverse:

                • LOAD 4: Generate a fixed set of 2000 dates somewhere in the future
                • LOAD 3: Calculate and add MonthStart, YearStart, Month and Year values to each date field
                • LOAD 2: Add various intermediate values while calculating a "Quarter" value for each date
                • LOAD 1: Add a dual (e.g. text and number) Quarter field to each date

                 

                That's about it.

                 

                Peter