Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
gshockxcc
Creator
Creator

Partial rename using field name map

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.

1 Solution

Accepted Solutions
effinty2112
Master
Master

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;

View solution in original post

2 Replies
effinty2112
Master
Master

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
Creator
Creator
Author

Andrew,

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