QlikTip #30: How to delete existing QVD files via load-script

In some delta-load scenarios it may be necessary to delete specific QVD files from within your load-script (QVD-Generator).

Imagine we have the following situation:

There is a delta-load which is loading the newest data from our database every day. Most days we only have to make an incremental load (“I”), but sometimes the database is refilled, so on these days we have to rebuild our QVD-storage completely. Therefore also previously loaded QVD-files should not be considered anymore):

Loading plan of the delta-load

Loading plan of the delta-load

So on 01.01.2011 and 04.01.2011 we have to make our full-load, all data within QVD-files on disk which a lower LoadNr than the current one should not be considered anymore …

Three different approaches:

When thinking about this requirement I have three ideas how to accomplish that:

  1. Delete the files with a macro called from the load script
  2. Delete the files by executing a batch statement
  3. Use a pure load-script statement without giving your script system-access and empty the QVD-files within your load-statement

(Continue to read to approach 3 because I’d recommend doing it this way! :))

Var. 1) Delete the files with a macro

The idea is simple:

  • Create a macro using the COM-object “Scrpting.FileSystemObject”
  • Call this macro from within your load-script

Macro:

public function DeleteFile(filePath)

	Set objFSO = CreateObject("Scripting.FileSystemObject") 
	Dim retVal 'as Boolean
	
	If objFSO.FileExists(filePath) Then
		Call objFSO.DeleteFile(filePath)
		retVal = true
	Else
		Set objFSO = nothing
		retVal = false
	End If 
		
	DeleteFile = retVal
	
end function

Load-Script:

SET vQvdFile = 'Sales_2011_01.qvd';
LET fileDeleted = (DeleteFile('$(vQvdFile)') = -1);

// 0 = false
// 1 = true
TRACE File deleted $(fileDeleted); 

To get this to work you have to lower the security-level of macro-execution and allow “System Access”:

Allow System Access for your Macro

Cons:

  • You have to use macros
  • You have to lower the security-level
  • More complex to use

Var. 2) Delete the files by executing a batch statement

Just add the following line of codes to your load-script:

EXECUTE cmd.exe /C del Sales_2011_01.qvd; 

For getting this to work you have to enable the following option:

Allow your load-script to execute external programs

Pros:

  • If you are already allowing external programs to be executed from your load-script, this is probably the easiest way!!!

Cons:

  • You have to lower the security-level

Var 3) Just empty the QVD files

Surprise, surprise, this is my favorite approach:

// ******************************************************************
// EmptyQvd is a generic procedure which deletes all records within a
// given QVD file.
// This is especially usefull if you do not need the records within
// this file anymore but do not want to rely on macros/batch-commands
// to delete the file.
// The fields will remain within the QVD file so you will not have any
// problems doing a wildcard load like "LOAD * FROM 'QVD_*.qvd'" ...
//
// ~~
// Usage:
// Call EmptyQvd('Sales_2009_02.qvd') // using a relative path
// or
// Call EmptyQvd('C:\QlikView\Sales_2009_02.qvd') // using an absolute path
//
// ~
// Author: Stefan Walther
// Date: 01/31/2011
// Version 1.0
// ******************************************************************
Sub EmptyQvd(qvdFilePath)

TRACE --;
TRACE Start EmptyQvd for file $(qvdFilePath);


// First check if the qvd-file exists
if (not IsNull(QvdCreateTime(qvdFilePath))) then
	TRACE ... file exists ...;
	
	// Check if the Qvd-file contains more than 0 records
	if(QvdNoOfRecords(qvdFilePath) > 0) then
		
		
		// Get one of the fields to create the fake "WHERE EXISTS" clause
		LET vFieldName = QvdFieldName(qvdFilePath,1);		
		
		// Now let's create a random value which cannot exist within 
		// the first field
		FakeFieldTable:
		LOAD
			// Create a fake-field which can never be found within 
			// the existing qvd
			'FakeField' & 'ABCDEFGHIJKLMNOPQRSTWXYZ' & 
			text(round(rand())) as $(vFieldName)
		AutoGenerate(1);
		
		
		// Create a random table name to prevent collisions with already 
		// existing tables in memory
		LET vTempTableName = '_TempTableName' & 
			text(round(rand() * pow(9,10)));
		
		// Load the existing QVD-file with and EXISTS clause which cannot
		// be found within the QVD-file; so the result will be an empty
		// inline table with all field definitions of the QVD-file
		$(vTempTableName):
		LOAD
			*,
			1 as loadEnabler
		FROM '$(qvdFilePath)' (qvd)
		WHERE Exists(Dim1)
		;
		
		// Re-Store the QVD-file, just with the field definitions but
		// without any data ...
		STORE $(vTempTableName) INTO $(qvdFilePath) (qvd);
		DROP TABLE $(vTempTableName);
		
		DROP TABLE FakeFieldTable;
		
		
	end if
	
	
else
	TRACE ... file '$(qvdFilePath)' does not exist;

end if

TRACE finished EmptyQvd;
TRACE --;

End Sub
// __________________________________________________________________

Call the script within your load-script:

Call emptyQvd('.\Sales_2011_10.qvd'); 
If a = 0 then
	TRACE ... TEST SUCCEEDED: QVD file has 0 records ...;
Else
	TRACE ... TEST FAILED: QVD file has $(a) records ... 
End if

Pros:

  • Doing so you will not have to change the required security level for executing your load-statement
  • But most important: this will not break scenarios with a wildcard load from QVDs (like “LOAD * FROM ‘Sales_*.qvd (qvd)’;”

Cons:

  • Sure the file will still remain on your hard disk …
Bookmark and Share

and tagged , , , , . Bookmark the permalink. Both comments and trackbacks are currently closed.

5 Comments

  1. Posted February 15, 2011 at 12:35 | Permalink

    Hello, Stefan.

    Firstly, I want to congratulate you!!! As we say in Russia – “Children are the flowers of life”
    and, of course, I congratulate you, that you have joined with QlikTech as an employee!

    Secondly, I would suggest a more compact way to save an empty QVD file. I think, that you can use Exists() function without FakeFieldTable. Like this:
    Sub EmptyQvd(qvdFilePath)
    ;
    TRACE –;
    TRACE Start EmptyQvd for file $(qvdFilePath);
    // First check if the qvd-file exists
    if (not IsNull(QvdCreateTime(qvdFilePath))) then
    TRACE … file exists …;

    // Check if the Qvd-file contains more than 0 records
    if(QvdNoOfRecords(qvdFilePath) > 0) then
    // Get one of the fields to create the fake “WHERE EXISTS” clause
    LET vFieldName = QvdFieldName(qvdFilePath,1);

    // Create a random table name to prevent collisions with already
    // existing tables in memory
    LET vTempTableName = ‘_TempTableName’ & text(round(rand() * pow(9,10)));

    $(vTempTableName):
    LOAD
    *,
    1 as loadEnabler
    FROM ‘$(qvdFilePath)’ (qvd)
    WHERE Exists([$(vFieldName)])
    ;
    // Re-Store the QVD-file, just with the field definitions but
    // without any data …
    STORE $(vTempTableName) INTO $(qvdFilePath) (qvd);
    DROP TABLE $(vTempTableName);

    end if
    else
    TRACE … file ‘$(qvdFilePath)’ does not exist;
    end if
    TRACE finished EmptyQvd;
    TRACE –;
    End Sub

    I checked it in QlikView 8.5, 9.0 and 10. It works in all versions.

    I hope this information will be useful for you

    kind regards, Anatoly.

    • Posted February 28, 2011 at 13:37 | Permalink

      Hi Anatoly,

      thank you for your feedback …

      I’ll digg into this and change my article if it really works!! Nice!

      Best regards
      Stefan

  2. Nilesh Nemade
    Posted March 11, 2011 at 11:50 | Permalink

    Hi Stefan,

    Wondering if the following method would be easier. Assuming we have checked the existence of qvd file, test.qvd, we can use the following with the same result i.e. emptying the qvd file.
    //===================
    TmpTbl:
    Load * from test.qvd(qvd)
    where 1=2;

    Store TmpTbl into test.qvd;

    Drop table TmpTbl;
    //====================

    Of course this can be converted to a subroutine if so desired.

    Regards,
    Nilesh

  3. Ralph
    Posted April 11, 2011 at 13:51 | Permalink

    Just implemented Version 2 in our DWH porject and it works perfect, thanks a lot Stefan!

  4. QlikView
    Posted June 2, 2014 at 11:40 | Permalink

    Hi Nilesh,
    Could you please confirm about using your approach when we have QVD which is very big(22GB approx)? Will you approach works efficientely? Please confirm!