I have a QVD's in the format QVDNAME.YYYY_WW.qvd. What I would like to do it to read all QVD's from a folder and sort by descending and load only latest 4 into my dashboard if the user wants to load latest 4 weeks of data(Which are in BOLD letters below). Could you please help me out for the same?
You can create the variable and for loop to take last 4 weeks from now onwards:
FOR i=0 to 3
LET vDate = DATE(Today() - $(i)*7);
LET vYrMonth = YEAR('$(vDate)') & '_' & WEEK ('$(vDate)');
LOAD * FROM QVDNAME.$(vYrMonth).qvd (qvd) ;
You can change the vDate variable to corresponding week calulcation.
Maybe something like this:
For each vFile in Filelist ('D:\QVdata\temp\Test\*.qvd')
'$(vFile)' as FileName,
makeweekdate(mid(subfield('$(vFile)','\',-1) ,9,4),mid(subfield('$(vFile)','\',-1),14,2)) As FileDate
Load FileName as FName
order by FileDate desc;
For i = 0 to 3
vFile = peek('FName',$(i),'Temp')
load * from '$(vFile)' (qvd);
drop table Files,Temp;
I have an additional question: I would like to load only the latest x files (controlled by a variable) and delete any older QVDs. How can I achieve that?
I think I need to change the formatting.My QVDNAME format it something like below. I mean exact size of the text.
Can you please help!