QlikTip #23: Deleting fields within a load-script dynamically

Some days ago I was asked how one could delete fields dynamically within/after a load-script in QlikView based on the name of the field/column.

So all columns for example with a prefix “F_” should be deleted after loading all tables from a database.

My first response was, that the “Table functions” (please have a look at the help-files of QlikView) could be used and I answered with the following pseudo-code using the builtin-QV-functions NoOfTables, TableName, NoOfFields, FieldName:

// ******************************************************************
// Note: this is just a pseudo-code …
// Note: this does not work, please finish reading this blog-post to 
// the end for getting the correct code
// ******************************************************************
FOR t = 0 TO NoOfTables() -1

    LET vTableName = TableName(t);

    // Loop through all fields/columns within the table
    FOR vColumnCounter = 1 TO NoOfFields(vTableName)
        
        LET vFieldName = FieldName(vColumnCounter, vTableName);	
				
        // If the prefix matches the beginning of the fieldname => drop the field
        if (left(vFieldName,2) = ‘F_’) then
            DROP FIELD $(vFieldName) FROM $(vTableName);
        end if

    NEXT	
NEXT 

During the dog-walk some hours later, I realized that this would not work in some/most cases (if we have more than one field/column matching our prefix per table):

• After deleting a column, the index of all columns with a higher index than the deleted one would change (decrease by one)
• So maybe we would not fetch all appropriate fields/columns

But how can we change the script to get it to work?

The answer is quite easy:

• We have to loop through all tables and fields/columns first (as demonstrated in the pseudo-code above)
• While looping we would have to save the information which columns should be dropped and
• Finally we would drop the “saved” columns

The first approach which came in my mind was just to save the information which tables should be dropped in a temporary-table, and this works nice:

// ******************************************************************
// Procedure for deleting all fields matching a given prefix.
// ~~
// Usage:
// Call DeleteFieldsByPrefix('F_') would delete all fields starting
// with "F_" within the whole QlikView-application
// 
// Further information:
//     http://www.qlikblog.at/?p=662
// ******************************************************************
Sub DeleteFieldsByPrefix(prefix)

// Loop through all tables
FOR vTableCounter = 0 TO NoOfTables() -1
	
	// fetch the tableName
	LET vTableName = TableName(vTableCounter);
	
	
	// Loop through all fields/columns within the table
	LET vNumOfFields = NoOfFields(vTableName);
	FOR vColumnCounter = 1 TO vNumOfFields
	
		LET vFieldName = FieldName(vColumnCounter, vTableName);					
		
		// If the prefix matches the beginning of the fieldName
		// we "save" the TableName and FieldName for later
		// dropping ...				
		if (left(vFieldName,len(prefix)) = prefix) then
			Temp_TableToDrop:
			LOAD
				'$(vTableName)' as TableName,
				'$(vFieldName)' as FieldName
			AUTOGENERATE (1);
		end if
	
	NEXT // fields looping

NEXT // table looping
	

	
FOR i = 0 TO NoOfRows('Temp_TableToDrop')-1
	
	LET vTableToDropFrom = peek('TableName',$(i),'Temp_TableToDrop');
	LET vFieldToDrop = peek('FieldName',$(i),'Temp_TableToDrop');

	TRACE Dropping field $(vFieldToDrop) from $(vTableToDropFrom) ...;
	DROP FIELD [$(vFieldToDrop)] FROM [$(vTableToDropFrom)];
	
	
NEXT

// Finally delete the temporary table
DROP TABLE Temp_TableToDrop;

// Reset/drop variables
LET i = null();
LET vTableName = null();
LET vTableCounter = null();
LET vColumnCounter = null();
LET vNumOfFields = null();
LET vFieldName = null();
LET vTableToDropFrom = null();
LET vFieldToDrop = null();

End Sub
// __________________________________________________________________