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.

[chandu441]

Hi Manish,

Little I have changed the Code. Just removed Data Functions in the code. Then VDate Value is getting worked for me.

Here is the below changed code for Reference.

Thanks Manish

[chandu441]

Hi Manish,

I ran your script as screenshot shared in the earlier reply. But the thing is it is executing long time when i run every time. I am not getting the concatenate and output as well. I am using only 22 QVD's for Testing purpose as of now.

Here is the code i am sending for you reference.

For Each Ext in 'qvd'

  For Each File in FileList ('E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\PE DEV\Backlog_History'&'\*.'&Ext)

  FileName: 

  Load 

   *, 

   IF(QVDNameDate = Date(Floor(QuarterEnd(QVDNameDate))),1,0) as Flag 

   ; 

  Load 

   '$(File)' as Name,  

   Date#(TextBetween(SubField('$(File)','\',-1),'_','.'),'YYYYMMDD') as QVDNameDate 

   Autogenerate 1; 

  Let vFlag = Peek('Flag',0,'FileName'); 

  Let vDate = Peek('QVDNameDate',0,'FileName');

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

  BackLog: 

  LOAD  * 

   FROM

   E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\PE DEV\Backlog_History\BACKLOG_$(vDate).qvd

   (qvd);

  ENDIF 

  Drop Table FileName; 

  Next File 

Next Ext

[chandu441]

Little Change of the Code is just concatenating the table with 0 as dummy otherwise it will create synthetic keys by loading same kind of column table repeat.

Worked code for me is here.

For Each Ext in 'qvd'

  For Each File in FileList ('E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\Backlog_History'&'\*.'&Ext)

  BackLog_LastDayofQuarter:

  LOAD 0 as DummyLoad AutoGenerate 0;

  FileName: 

  Load 

   *, 

   IF(QVDNameDate = Date(Floor(QuarterEnd(QVDNameDate))),1,0) as Flag 

   ; 

  Load 

   '$(File)' as Name,  

   Date#(TextBetween(SubField('$(File)','\',-1),'_','.'),'YYYYMMDD') as QVDNameDate 

   Autogenerate 1; 

  Let vFlag = Peek('Flag',0,'FileName'); 

  Let vDate = Peek('QVDNameDate',0,'FileName');

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

  Concatenate(BackLog_LastDayofQuarter)

  LOAD  *  ,

  FileBaseName() as QVD_Name

   FROM

   E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\Backlog_History\BACKLOG_$(vDate).qvd

   (qvd);

  ENDIF 

  Drop Table FileName; 

  Next File 

Next Ext