How To drop multiple fields using pattern (wildcard)
I was looking for a good neat solution to remove multiple fields from my QlikView data model with similar names. These were temporary fields, needed during execution of the load script but no longer needed once script is finished. Unfortunately QlikView 11 does not allow something like that:
DROP FIELD TempField*;
Quick Google search revealed quite a few different techniques but I really liked the one suggested by a wonderful QlikView guru John Witherspoon.
The script below scans all the tables and will drop fields with specified name pattern. You can pass multiple patterns as well like I am doing below with TempField*
and TempDate*
:
//drop fields using wildcard - based on https://community.qlik.com/thread/49803
FOR vTableNumber = 0 TO NoOfTables()-1
LET vTableName = tablename(vTableNumber);
FOR vFieldNumber = NoOfFields('$(vTableName)') TO 1 STEP - 1
LET vFieldName = fieldname(vFieldNumber,'$(vTableName)');
// list of wildcard pattern here for field names to drop:
IF wildmatch('$(vFieldName)','TempField*','TempDate*') THEN
TRACE Dropping Field [$(vFieldName)] from Table [$(vTableName)];
DROP FIELD [$(vFieldName)];
ENDIF
NEXT
NEXT
I also like to remove variables I do not need after the script is finished. In this case:
LET vTableName=;
LET vTableNumber=;
LET vFieldName=;
LET vFieldNumber=;
You might consider putting this code into a subroutine and pass field name patterns to it or table name to drop fields only from that table.