I have a folder ..\QVD_DataFiles in which I have monthly revenue files :
All files starting 201401 are refreshed daily , so file create timestamp cannot be used.
Also , every month a new file gets added here.
I want to loop through all files in this folder , but specifying start and end date conditions
Lets say , I don't want data from 2014 files.
In that case ,suppose start_date=201504
End Date will always be current year month (201702)
How to concatenate data , from files , from specified date ranges?
Solved! Go to Solution.
This is how I implemented it:
for i=0 to $(vDiffYear) step 1
LET vYearBase = $(vStartYear) +$(i);
for j = 1 to 12 step 1
LET vMonthBase = Num($(j),'00');
LET vYearMonth = $(vYearBase)$(vMonthBase);
TRACE 'YearMonth is' $(vYearMonth);
if isnull(filetime('$(lQVDPath)QVD_Revenue_Details_'&$(vYearMonth)&'.qvd')) Then
TRACE ' The file for ' $(vYearMonth);
TRACE 'does not exist';
from $(lQVDPath)QVD_Revenue_Details_$(vYearMonth).qvd(qvd) ;
STORE Revenue_Details into $(lQVDPath)QVD_MD_Revenue_Details.qvd(qvd);
DROP TABLE Revenue_Details;
Try like this loop
for yeartoload=2015 to 2016
for month= 04 to 12
let vmakedate= makedate($(yeartoload),$(month),1) ;
from Revenue_Details_$(vmakedate) ;
The thing is , my end month is always the current month,which i don't want to hardcode.
If I use :
LET $(vEndMonth)=num(Month(Today()), I get '2' , whereas I wanted '02'
Could you help ?
I think the loop only gets the months 04 to 12 every year if used that way. May be we need to loop the incomplete year 1st and the go with Month = 01 to Num(Month(Today()),'00') for remaining years.
Since the start month is also not fixed , using inner loop to start with 4 would not be a correct option.
Is there a possibility to loop from 201504 till 201702 ,incrementing yearmonth by 1 each time?
Try some thing below
FOR EACH vFile in ('$(vPath)\*.xlsx')
FILENAME() as FileName,
Date(MakeDate(Left((KeepChar(FileName(),1234567890)),4),Right((KeepChar(FileName(),1234567890)),2)),'YYYYMM') As Date1,
FILENAME()&'-'&FileTime() as File_Key
Load *,FileTime() As FileTime Resident MAINLOAD where num(Date1)>Date#('201402','YYYYMM') and num(Date1)<=num(Date(Today(),'YYYYMM')) ;
Drop Table MAINLOAD;
Try something like below
for i = Monthstart('01/04/2015') to Monthstart(Today)
Let Suffix = Year($(i))& Num(Month($(i)),'00');
from Revenue_Details_$(Suffix) ;
Please refer below mentioned URL, i think it will definitely help you.
Please remember to mark this as "helpful" & "correct answer" if your query has been solved.
This will help users identify the answers should they come across this thread in the future.