last two months data from QVD with wildcard using

[abhaskar1]

i want to reload last two months data from QVD, need to mention a condition at QVD path

by using resident load it is working please suggest me how to resolve this.

my date format is YYYYMMDD.

Adj_Temp:
LOAD
'' as AWTaxGL
AutoGenerate(0);
Concatenate(Adj_Temp)
Load *
FROM
$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver*.qvd
(qvd)

[rubenmarin]

Hi, you can use a variable to store the initial date and use it on the where clause:

LEt vStartDate=Date(AddMonths(today(),-2),'YYYYMMDD');
...
LOAD * from... (qvd) where DateField>=$(vStartDate);

[mikaelsc]

the problem is that you're going to query all qvd's then... 

i'd try to generate all dates that you need to load (in a sort of calendar table) 

and using a loop to peek every date value, store it as variable, load qvd, next date,  etc

[abhaskar1]

thanks for response rubenmarin.and mikaelsc

using * as wildcard match at qvd path , so it's not reloading

any other solution please.

[rubenmarin]

Hi, the use of a wildcard should allow the use of a where condition. We need more info of what you are trying to do and wich error you are getting.

[abhaskar1]

Thanks for response Rubenmarin.

below is my qvd folder and script.

instead of loading all qvd files need to reload only last 60 days files from QVD folder in script at QVD path.

[mikaelsc]

probably something like this... 

let vMinDate = num(today()-60); 

let vMaxDate = num(today()); 

tempCalendar: 

load 

date($(vMinDate)+iterno()-1,'YYYYMMDD') as tempDate

autogenerate(1) 

while $(vMinDate)+iterno()-1<=$(vMaxDate) ; 

let vNumberOfDates = noofrows('tempCalendar')-1; 

for i = 0 to $(vNumberOfDates)

let vDate = peek('tempDate',$(i),'tempCalendar'); 

Adj_Temp:
Load *
FROM
$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver$(vDate).qvd
(qvd); 

next i

[abhaskar1]

Thanks for response Mikaelsc.

getting below error while loading script

[rubenmarin]

Hi, that's telling you that there is no qvd for that particular date, do you have a qvd for every day? If yes, check the path and qvd names.

You can add a check to confirm if a file exists before loading it:

IF IsNull(QvdCreateTime('$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver$(vDate).qvd'))=0 THEN
  Adj_Temp:
  LOAD..

ENDIF

BTW @mikaelsc answer is a good one, but I think it can be simplified to:

FOR i = 0 to 60 // or 59

  LET vDate = date(Today()-$(i),'YYYYMMDD'); 
  IF IsNull(QvdCreateTime('$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver_$(vDate).qvd'))=0 THEN
    Adj_Temp:
    Load *
    FROM
    [$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver_$(vDate).qvd](qvd); 
  ENDIF
NEXT i

[abhaskar1 · Accepted solution]

Now it's working fine 

Thanks to @rubenmarin @mikaelsc