8 Replies Latest reply: Mar 1, 2017 8:30 PM by m m RSS

    For Loop

    m m

      I need to loop 3 times thru each month. days 01-10, 11-20, 21-31

       

      //Starting Variable to pass YYMM

      vStartYYMM='201702'

       

       

      Loop1

      vStartDate=$(vStartYYMM)01

      vStartDate=$(vStartYYMM)10

       

      Loop2

      vStartDate=$(vStartYYMM)11

      vStartDate=$(vStartYYMM)20

       

      Loop3

      vStartDate=$(vStartYYMM)21

      vStartDate=$(vStartYYMM)31

       

       

      How do you create the days portion via loop.

        • Re: For Loop
          Massimo Grossi

          Why do you need 3 loop?

           

           

          LET vStartYYMM='201702';

           

          FOR i=1 to 10

            LET vStartDate=$(vStartYYMM)& num($(i), '00');

            TRACE $(vStartDate);

          NEXT

           

          FOR i=1 to 10

            LET vStartDate=$(vStartYYMM)& num(10 + $(i), '00');

            TRACE $(vStartDate);

          NEXT

           

          FOR i=1 to 11

            LET vStartDate=$(vStartYYMM)& num(20 + $(i), '00');

            TRACE $(vStartDate);

          NEXT

            • Re: For Loop
              m m

              Because i am doing data pull by increments.

               

              for u = 1 to 31 step 11

               

              Let vStartu= $(vmonth0)&num($(u),'00');

              Let vEndu= $(vmonth0)&num(9+$(u),'00');

               

              trace 'vStartu = '$(vStartu);

              trace 'vEndu = ' $(vEndu);

               

              next

               

              so above works to a certain degree but not exactly right.

               

              OUTPUT

              'vStartu = '20170201

              'vEndu = ' 20170211

               

              'vStartu = '20170212

              'vEndu = ' 20170222

               

              'vStartu = '20170223

              'vEndu = ' 20170233

               

               

              Last vEndu date is exceeding 31 days.

                • Re: For Loop
                  Aar Kay

                  Will this work for You??


                  LET vStart=NUM(MonthStart(TODAY()));

                  TEMP:

                   

                   

                  LOAD

                    $(vStart)+ROWNO()-1 AS NumDate,

                    Date($(vStart)+ROWNO()-1,'DDMMYYYY') as Date

                    AutoGenerate

                    Num#(Date(MonthEnd(TODAY()),'DD'));

                   

                   

                  DateLoop:

                  Load Distinct

                  If(Left(Date,2)<='10',Makedate(Year(Date),Month(Date),10),

                        If(Left(Date,2)<='20' and Left(Date,2)>'10',Makedate(Year(Date),Month(Date),20),

                              If( Left(Date,2)>'20',Makedate(Year(Date),Month(Date),num#(Date(MonthEnd(Date),'DD')))

                              )

                        )

                  ) as EndDate,

                  If(Left(Date,2)<='10',Makedate(Year(Date),Month(Date),1),

                        If(Left(Date,2)<='20' and Left(Date,2)>'10',Makedate(Year(Date),Month(Date),11),

                              If( Left(Date,2)>'20',Makedate(Year(Date),Month(Date),21)

                              )

                        )

                  ) as StartDate

                  Resident TEMP;

                  Drop table TEMP;

                  • Re: For Loop
                    Massimo Grossi

                    LET vMonthStart = MonthStart(Date#('$(vStartYYMM)', 'YYYYMM'));

                    LET vMonthEnd = MonthEnd(Date#('$(vStartYYMM)', 'YYYYMM'));

                    TRACE $(vMonthStart);

                     

                    FOR i=0 TO 2

                      LET vStartDate = Date('$(vMonthStart)' + 10 * i, 'YYYYMMDD');

                      TRACE $(vStartDate);

                     

                      IF (i=2) THEN

                      LET vEndDate = Date('$(vMonthEnd)', 'YYYYMMDD');

                      ELSE

                      LET vEndDate = Date('$(vMonthStart)' + 10 * i + 9, 'YYYYMMDD');

                      ENDIF

                      TRACE $(vEndDate);

                    NEXT

                     

                    1.png