QlikView Blog by Stefan Walther

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

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 …