1 Reply Latest reply: Nov 2, 2011 5:47 AM by Kaushik Solanki RSS

    Use Variable for Calander Range in AutoGenerate

      Hi,

       

      I am loading a period table which just has the financial year values in which I load as follows:

      PERIODS:

      LOAD

           Year

      FROM

      $(zDataPath)\Periods.qvd(qvd);

      Then to determine the amount of Years I have available I do the following:

      PERIOD:

      Load     

          Max(Year) - Min(Year) + 1 as YR_Range

      Resident PERIODS;

       

      Now I use this code for the Calander:

      Load

          TODAY()-ROWNO()+365 AS PeriodDate,

          YEAR(TODAY()-ROWNO()+365) AS YEAR,

          MONTH(TODAY()-ROWNO()+365) AS MONTH,

          DAY(TODAY()-ROWNO()+365) AS DAY,

          YEAR(TODAY()-ROWNO()+365)*100 + MONTH(TODAY()-ROWNO()+365) AS YM,

          YEAR(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "FIN YR",

          MONTH(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "PERIOD",

          YEAR(ADDMONTHS(TODAY()-ROWNO()+365,3))*100 + MONTH(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "FIN YP"

      AUTOGENERATE (365*3);

       

      My problem is that for the AutoGenerate command I want to load the amount of years based on what is the Max - Min of Year plus 1 into a variable and use that variable for the AutoGenerate command, which should be something like this

      AUTOGENERATE (365*v_Range);

       

      Regards

      Jimmy

        • Use Variable for Calander Range in AutoGenerate
          Kaushik Solanki

          Hi,

           

              Try this code.

           

           

              

          PERIOD:

          Load     

              Max(Year) - Min(Year) + 1 as YR_Range

          Resident PERIODS;

           

          let v_Range = peek('YR_Range',0,'PERIOD');

           

          Load

              TODAY()-ROWNO()+365 AS PeriodDate,

              YEAR(TODAY()-ROWNO()+365) AS YEAR,

              MONTH(TODAY()-ROWNO()+365) AS MONTH,

              DAY(TODAY()-ROWNO()+365) AS DAY,

              YEAR(TODAY()-ROWNO()+365)*100 + MONTH(TODAY()-ROWNO()+365) AS YM,

              YEAR(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "FIN YR",

              MONTH(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "PERIOD",

              YEAR(ADDMONTHS(TODAY()-ROWNO()+365,3))*100 + MONTH(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "FIN YP"

          AUTOGENERATE (365*$(Range));

           

          Regards,

          Kaushik Solanki