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
