Removing fields with a wildcard

This particular recipe was inspired by a client of mine who was loading data from a shared set of QVDs. There were a lot of fields in the QVDs that he didn't need and he was wondering if he could; rather than manually pruning the load statement, automatically remove fields based on a wild card (all the fields that he wanted removed had the word CUSTOM_ at the beginning).

Unfortunately, we cannot use wildcards in the Drop Fields statement. There are a couple of useful functions that we can use to achieve this.

Getting ready

Load the following script:

MyTable:
LOAD * INLINE [
F1, F2, F3, F4
1, 2, 3, 4
];

Rename Field F1 to NewField;
Rename Field F3 to NewField2;

How to do it...

Use these steps to see how to remove fields with a wildcard:

1. Add the following script:

   Let i = 1;
   
   Do While i <= NoOfFields('MyTable')
   
     Trace Getting Field $(i) From MyTable;
     
     Let FieldName = FieldName($(i), 'MyTable');
     
     Trace FieldName = $(FieldName);
     
     If '$(FieldName)' Like...