topic Re: How to Load privious qvd? in QlikView

How to Load privious qvd?

d_koti24 — Mon, 29 Jun 2015 13:08:31 GMT

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.

Re: How to Load privious qvd?

pokassov — Mon, 29 Jun 2015 13:14:43 GMT

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;

Re: How to Load privious qvd?

jonathandienst — Mon, 29 Jun 2015 13:18:00 GMT

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

Re: How to Load privious qvd?

d_koti24 — Mon, 29 Jun 2015 13:24:54 GMT

Hi Jonathan,

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

Regards.

Re: How to Load privious qvd?

jonathandienst — Mon, 29 Jun 2015 13:28:22 GMT

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.

Re: How to Load privious qvd?

d_koti24 — Mon, 29 Jun 2015 13:45:56 GMT

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,

Re: How to Load privious qvd?

Peter_Cammaert — Mon, 29 Jun 2015 14:17:00 GMT

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

Re: How to Load privious qvd?

rubenmarin — Mon, 29 Jun 2015 14:20:46 GMT

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

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