How to Load privious qvd?

[d_koti24]

Hi All,

I have 5 qvds that 5 qvds contains the monthly data.my requirement is i want load the previous qvd's dynamically.

ex:

space201502.qvd

space201503.qvd

space201504.qvd

space201505.qvd

space201506.qvd ----currentmonth

for analysis purpose instead of loading all months data,i want load the only previous 2 months data i.e

space201504.qvd

space201505.qvd

and concatenate to space201506.qvd data.

when july will come i want load

space201505.qvd

space201506.qvd

and concatenate to july data.

when aug comes i want load

space201506.qvd

space201507.qvd

and concatenate to aug data. like this...

how to achive this dynamically??

how to get the last2 previous qvd's name?

regards,

kd.

[pokassov]

Hello!

let vFile='space'&date(MonthStart(AddMonths(Today(),-1)),'YYYYMM');

t1:

load *

from $(vFile).qvd;

let vFile='space'&date(MonthStart(AddMonths(Today(),-2)),'YYYYMM');

concatenate(t1)

load *

from $(vFile).qvd;

let vFile='space'&date(MonthStart(Today()),'YYYYMM');

concatenate(t1)

load *

from $(vFile).qvd;

[jonathandienst · Accepted solution]

If you use a ForEach vFile in FileList('\\server\qvdfile\space*.qvd construct and parse the date from the filename contained in vFile, then this should be simple enough. Something like:

Let vMinMonth = MonthStart(Today(), -2);

ForEach vFile in FileList('\\server\qvdfiles\space*.qvd')

  Let vBaseFilename = Substring(vFile, '\', -1);

  Let vDate = Date#(Mid(vBaseFilename, 6, 6));

  If vDate >= vMinMonth Then

       Space:

       LOAD *

       FROM [$(vFile)] (qvd);

  End If

Next

[d_koti24]

Hi Jonathan,

how to concatenate this months to current month qvd.can u please provide me clear script.

Regards.

[jonathandienst]

If the field names are the same, it will auto concatenate.

If you load the current month first, then add a concatenate keyword with the existing table name to the load statement:

  If vDate >= vMinMonth Then

       concatenate(Data)

       LOAD *

       FROM [$(vFile)] (qvd);

  End If

If you load the current month last, add a concatenate to that load.

[d_koti24]

For Each vFile in FileList ('\\gma\qv_storage_dev\USERS\99_Koteshwar\space\MONTHLY_QVDS\*.qvd')

  Let vBaseFilename = Substring(vFile,'\',-1);

i am getting script error here...

Let vBaseFilename = Substring(vFile,'\',-1); ---vFile script error here.

regards,

[Peter_Cammaert]

Maybe you don't have any qvd's in that directory?

Add the following statement between FOR EACH ... and LET vBaseFileName...

TRACE >>> Current filename = '$(vFile)' ;

and run your script again. Then check the log file.

Peter

[rubenmarin]

Hi koti, try using single quotes for the first parameter of substring:

Let vBaseFilename = Substring('$(vFile)','\',-1); ---vFile script error here.