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:

[qvl]
// ******************************************************************
// 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
[/qvl]

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:

[qvl]
// ******************************************************************
// 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
// __________________________________________________________________
[/qvl]