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 // __________________________________________________________________
One Comment