4 Replies Latest reply: Oct 30, 2014 11:07 AM by pablo masotta RSS

    Loop load with month name

      Hi all!

       

      I have a load statement like

       

      DATES_BY_MONTH:

      LOAD:

      MonthName( (monthend(addmonths (today(),-1,1))) ) as Date

       

      RESIDENT MyTable

      Concatenate

      LOAD:

      MonthName( (monthend(addmonths (today(),-2,1))) ) as Date

      RESIDENT MyTable

       

      Concatenate

      LOAD:

      MonthName( (monthend(addmonths (today(),-3,1))) ) as Date

       

      RESIDENT MyTable

       

      and so on...

       

       

      I need to do this concatenation for each month name available in my date field on "MyTable". I would to set a loop that browse these months names and make the load dynamically. In the example I'm adding a negative number for each month name on the addmonths function.

       

      Is this possible?

       

      Thanx you all!!!

       

      Pablo

        • Re: Loop load with month name
          Massimo Grossi

          for i=-1 to -12 step -1

           

               DATES_BY_MONTH:

               LOAD

               MonthName( (monthend(addmonths (today(),$(i),1))) ) as Date

               RESIDENT MyTable;

           

          next;

          • Re: Loop load with month name
            Marco Wedel

            Hi,

             

            can you describe a bit more in detail what you like to achieve with this script?

            Maybe with sample data for input as well as output.

             

            One remark though:

             

            MonthName( (monthend(addmonths (today(),-3,1))) ) as Date
            


            should return no different results than


             

            MonthName(AddMonths(Today(),-1)) as Date

             

             

            as the MonthEnd function is rendered useless by the MonthName function as well as the third parameter of the AddMonths function has no effect due to the enclosing MonthEnd function.

             

            hope this helps

             

            regards

             

            Marco

              • Re: Loop load with month name

                Hi again marcowedel!

                 

                I want to obtain a snapshot table with a date field ("Date") not related with my date transactional field (CreationDate). I can do it well with the loop syntax provided by Massimo.

                I think this will be perfect for now!

                 

                Thanx!!

                 

                for i=-1 to -3 step -1

                TicketsSnapshot:

                LOAD

                MonthName( (monthend(addmonths (today(),$(i),1))) ) as Date,

                // TICKETS CREATED ON PREVIOUS MONTH

                Count (if(MonthEnd(CreationDate)= (monthend(addmonths (today(),$(i),1))), TicketNumber)) as Created,

                // TICKETS ACCUMULATED UNTIL PREVIOUS MONTH

                Count (if(MonthEnd(CreationDate)<= (monthend(addmonths (today(),$(i),1))),TicketNumber)) AS [Accumulated Tickets]

                Resident TICKETS;

                next;

                 

                 

                Tickets.jpg