0 Replies Latest reply: Dec 5, 2016 12:00 PM by Azmina Virani RSS

    Increment Month in While loop

    Azmina Virani

          LET vDate = null;

          LET vStartDate = MonthStart(AddMonths(Date(Today()),-21));

              LET vEndDate = Monthend(AddMonths(Date(Today()),-1));

              LET vDDStartDate = Num(Date#(MonthEnd(addmonths(today()-1,-2)),'MM/DD/YYYY'))-3;

              LET vTest =Date(monthend($(vEndDate)));

        

             

             Do while vStartDate <= vEndDate   // Need the data from past 21 month to last month.

       

       

          FOR vDate =  date(AddMonths('$(vStartDate)',-2),'YYYY-MM-DD') to date(MonthEnd('$(vStartDate)'),'YYYY-MM-DD'); // FOR each month i need to look back at 2 months of data to get repeat customer in the last 3 months

       

          LET vDate = Date($(vDate), 'YYYY_MM_DD');

              Customer:         

              LOAD     ID

                    FROM [lib://QVD/QVD/E_Cust$(vDate).qvd](qvd);   // FILES ARE SAVED DAILY FOR PREVIOUS DAY _ NEED TO LOOP THROUGH 3 month of data to get all cust and ID

       

          Next vDate;

       

       

       

              CUSTFINAL:

                  LOAD DISTINCT

                          ID,

                      date(MonthEnd('$(vStartDate)'),'YYYY-MM-DD') as [Month Date]    // save the date of the month ran

                    RESIDENT Customer;       

                 

       

       

       

                  CONCATENATE (CUSTFINAL)

            

                       LOAD *

                   FROM [lib://QVD/A.qvd](qvd);

                 

                  STORE (CUSTFINAL) into [lib://QVD/A.qvd] ;

               

         

       

                DROP TABLES Customer ;       

                                     

          LET vStartDate = date(AddMonths('$(vStartDate)',1),'YYYY-MM-DD');   // This is the issue - not when the loop run i want the next month data to be extracted- when but once increment it - the top vStart Vaiable replaced this date

       

           LOOP