Loading QVD's if field exists or not condition.

[chandu441]

Hi All,

I am loading multiple QVD's from History QVD's Data Folder. Where i have to load all the QVD's and concatenate.

Before loading I have to check the qvd is having Adjustment_Type field is there are not if it is there I have to load that qvd with where Adjustment_Type = 'B' AND Adjustment_Type = 'Z';

Problem is I have to load the QVD if Adjustment_Type field is missing in the qvd. Then my script is getting error to load. So help me out of this guys.

Here is the Code for reference which I tried:

BackLog:

LOAD 0 as Dummy AutoGenerate 0;

For Each Ext in 'qvd'

  For Each File in FileList ('E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\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

  If($(vDate) <= '20160630') Then

  Concatenate(BackLog)

  LOAD  *  ,

  FileBaseName() as QVD_Name

  FROM

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

   Where ADJUSTMENT_TYPE<>'B' AND ADJUSTMENT_TYPE<>'Z';

  ENDIF

  ENDIF

  Drop Table FileName;

  Next File

Next Ext

Thanks

Satya

[petter]

ALTERNATIVE 1:

Every QVD-file has an XML-header. The XML-header contains the metadata for the QVD-file. The metadata has all the information you need about the fields in the QVD.

The good news for you is that you can open a QVD-file as an XML-file from QlikView not only as a regular QVD-file. Then you will read only the XML-header and get the info you need.

So I suggest for you that you have a first iteration through all your QVD-files reading the XML-header to get a table full of metainfo and column/field-info about them and then you can even have load script logic that dynamically generate the load statements to get the files you want and only the fields you want.

This is an example of an XML-header from one of my QVD-files opened in Notepad++:

From your load editor you select TABLE FILE and point to your QVD. Then you switch to XML instead of QVD on the left hand side of the dialog box like you see below:

The resulting load script becomes this:

Building on this you can make a more dynamic and intelligent load script to make decisions on which QVDs to load and load ing them correctly.

ALTERNATIVE 2:

You could always use CONCATENATE LOAD to force the QVDs to be loaded into on table and then afterwards delete the "subset" of rows that corresponds to the tables you don't need. This approach is a bit more brute force but it is very easy and quick to implement compared to the above suggestion with reading metadata.