Partial rename using field name map

[gshockxcc]

I've been searching the community, but can't seem to find a mapping related post that covers exactly this topic.

I want to load a QVD and do a partial rename on all the fields.

The fields are names like this FIELDNAME_Current.

I want to rename them all to FIELDNAME_Prior.

I've tried to use a For loop for this, and it only works when loading the fields from an Excel file.  It doesn't seem to work when loading from a QVD.

[effinty2112 · Accepted solution]

Hi Kristan,

Have a look at this script.

After you've loaded from your qvd(s) this script will look for field names ending in '_Current' when it does find one it adds a rowto a mapping table that's used on the final line of the script. I've tested this on a qvd with 25 fields all named ..._Current and all are named ..._Prior in the resulting resident table.

For i = 0 to NoOfTables() - 1

TRACE  Looking at table : $(vTableName);

Let vTableName = TableName($(i));

  for k = 1 to NoOfFields('$(vTableName)')

  Let vFieldName = FieldName($(k),'$(vTableName)');

  if Right('$(vFieldName)',8) = '_Current' then

  Let vNewFieldName =  Left('$(vFieldName)',len('$(vFieldName)')-8) & '_Prior';

  FieldNameMap:

  Mapping

  LOAD

  '$(vFieldName)' as FromFieldName,

  '$(vNewFieldName)' as ToFieldName

  AutoGenerate 1;

  end if;

  NEXT

Next

RENAME Fields using FieldNameMap;

[gshockxcc]

Andrew,

     Thanks for your quick response, and for the help.  Regards, - Kristan