Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor

Update Old qvd's with data


 I have an application which loads 94 qvd files  and we keep getting new columns added into our qvd . In current code ,all columns existing  in the qvd from the beginning are assigned a name and set to a variable called Fields. for the new columns that come up ,first  the header is loaded and then a check is made to see if the column exists in a particular qvd. If not then its updated with 'NA' values and these new fields are then added to the table. Now the problem is that the no. of new columns are increasing day by day and the number of checks for the new columns are slowing down the app. How can i improve this code to make the application work faster.

LET vExtractCount=0;
LET vMaxFileCount=94;
LET vFileName = '';
LET vCurrentDate = Today();
LET v2MonthCutOff = AddMonths(today(),-2);
LET v6MonthCutOff = AddMonths(today(),-6);
LET v2YearCutOff = AddMonths(today(),-24);
LET vSubFolder = 'QVD_Folder\';


Do while vExtractCount < vMaxFileCount
// Extract for current Date
let vFileName = 'BTR_'&Date#(vCurrentDate,'YYYY-MM-DD')&'.qvd';
IF len(FileSize(vFilePath &vSubFolder &vFileName)) > 0 THEN

// setting initially with always included variables

SET fields =
"COL_A as [A],
COL_B as [B],
COL_C as [C],
COL_D as [D],
COL_E as [E],

// Load only the header row
FROM $(vFilePath)\$(vSubFolder)\$(vFileName) (QVD);

//Check if the column exists in the temp table 'COLS'
IF (FieldNumber('COL_X','COLS')>0) THEN
SET fields = $(fields),[COL_X] as [X] ;
SET fields = $(fields),'N/A' as [X];

IF (FieldNumber('COL_Y','COLS')>0) THEN
SET fields = $(fields),[COL_Y] as [Y] ;
SET fields = $(fields),'N/A' as [Y];



//Load the fields
From $(vFilePath)\$(vSubFolder)\$(vFileName) (QVD);

//Update the count
let vExtractCount = vExtractCount + 1 ;


//Swich to the next QVD
if vCurrentDate > v2MonthCutOff THEN
LET vCurrentDate = Date(vCurrentDate-1);
elseif vCurrentDate > v6MonthCutOff THEN
LET vCurrentDate = Date(vCurrentDate-7);
elseif vCurrentDate > v2YearCutOff THEN
LET vCurrentDate = AddMonths(vCurrentDate,-1);
LET vExtractCount = 1000;


1 Reply

Re: Update Old qvd's with data

Without deep diving into your code, but based on your description, I don't see any "easy" way to dramatically improve performance here. 

If I understood right, you cannot just CONCATENATE QVDs since you need that "NA" value for the field which will not exist into older files and you cannot join to previous QVDs and reduce the number of QVDs since you don't know if or when new fields will be coming.

I would suggest, though, why the number of columns in such QVDs must increase versus why the new data cannot have a value to be linked in the data model with older QVDs.

Additionally, I would suggest loading an empty table including the new field names and concatenating this with older fields, and storing this new table into a new QVD, so the number of checks tomorrow will be the same or very similar, i.e.: you are not increasing one field every day, since the previous day has already all previous fields added.

If you really need that "NA" you could reload that new QVD with both new and old fields and check the length of every field and assign 'NA' If(Len(Field) = 0).

Even if the script took the same time to execute, making any table bigger means more values, more resource consumption and higher footprint, which you cannot avoid, and probably, over time, longer response times and poorer user experience.