Loading Multiple QVD's from the Last date of Previous Quarters in that over all QVD's Path folder.

[chandu441]

Hi All,

I have to import Previous Quarter Last Day (Date) Data from a QVD's Folder which is having every day backup QVD's and Concatenate all the qvds as one source in the dashboard.

Currently i am having 'N' of Qvds in the folder in that I have to take only Last Date of all the Previous Quarters data dynamically every day when the application getting reload.

QVD naming convention you can see below Screenshot. In the Folder I have to search the dates of last day of every quarter in the file name to load.

Please suggest me a solution which takes less time to load.

[MK_QSL]

When you say you are having every day backup as QVD... is there any date getting missed?

Why I am asking, because if no date is missing, you can simple create calendar and find out last date of every quarter using QuarterEnd function.

[chandu441]

Hi Manish,

There is no missing date data in the QVD's as you asked. But suggest me the solution if some of the QVD Date is missing in the folder in anycase.

I can't even confirm how many years of data we have stored in the folder to loop. It has to fetch the data according tot he files which have been available in the folder itself

Cheers,

Chandra

[MK_QSL]

Will definitely help you.

Let me know what exactly you want to achieve.

Only to find out QVD names having last date of every Quarter?

or you want to find out and load them as well.

[chandu441]

Hi,

I have to findout those qvds and load all the qvds as one table (Concatenate)

[MK_QSL]

So let's say example..

you want to find out...

BACKLOG_20150331

BACKLOG_20150630

BACKLOG_20150930

BACKLOG_20151231

BACKLOG_20160331

BACKLOG_20160630

BACKLOG_20160930

BACKLOG_20161231

and so on.. and want to load them and concatenate them..

What if we don't have QVD for last date?

i.e.BACKLOG_20160331.. means we don't have QVD for 31/03.. 30/03

But instead we have BACKLOG_20160329

Do you want to load 29/03 or not?

[chandu441]

No i don't want to load QVDS which are not last date of the quarter. I don't want 29/03. If last date of qvd is not there just go for the available last date quarter qvds.

Thanks

[MK_QSL · Accepted solution]

For Each Ext in 'qvd'


  For Each File in FileList ('C:\QlikView\QVD'&'\*.'&Ext)

  FileName:
  Load
  *,
  IF(QVDNameDate = Date(Floor(QuarterEnd(QVDNameDate))),1,0) as Flag
  ;
  Load
  '$(File)' as Name,
  Date(Floor(Date#(TextBetween(SubField('$(File)','\',-1),'_','.'),'YYYYMMDD'))) as QVDNameDate
  Autogenerate 1;

  Let vFlag = Peek('Flag',0,'FileName');
  Let vDate = Date(Date#(Peek('QVDNameDate',0,'FileName'),'DD/MM/YYYY'),'YYYYMMDD');

  If('$(vFlag)' = 1) Then

  Data:
  LOAD
  *
  FROM
  QVD\BACKLOG_$(vDate).qvd
  (qvd);


  ENDIF


  Drop Table FileName;

  Next File
Next Ext

[chandu441]

Hi Manish,

Thanks for your help, I am trying to implement the same with changing the QVD folder path. I am getting the error below screenshot you can see. Variable vDate is not finding the value itseems. Can you please help me in the issue.

Cheers,

Satya Chandra

[MK_QSL]

Can you upload your qvw script here?