Rename all fields programmatically from each table in script

[kmstephenson]

Hi All,

What is the best way to rename programmatically all fields in a given table with a "xx_" prefix related to the table name (but not the table name itself - and therefore not an alias or QUALIFY statement)?

Thanks!

[kmstephenson]

Hi Rob,

The answers to your questions are primarily table and variable naming standards within the organization. QUALIFY isn't used in production applications (fields are just renamed when necessary) so the primary goal is to continue to align within standards but programmatically rename all required fields with the desired prefix. Most scenarios we work with don't include duplicate field names across tables, so this data model is unique in that sense for us.

Thanks for your help with this! 

[rwunderlich]

Here'a another version that let's you specify keys to exclude and specific table prefixes:

TempKeys:
LOAD * Inline [
Key
Num
]
;
TablePrefixMap:
Mapping
LOAD * Inline [
Name, Prefix
Transactions, TR_
Characters, DI3_
ASCII, II-
]
;
For tableIdx = 0 to NoOfTables() - 2
  Let vTableName = TableName($(tableIdx));
  For fieldIdx = 1 to NoOfFields('$(vTableName)')
    LET vFieldname = FieldName($(fieldIdx), '$(vTableName)');
    TempRenameMap:
    Load
      '$(vFieldname)' as oldname, 
      ApplyMap('TablePrefixMap','$(vTableName)', '') & '$(vFieldname)' as newname
    AutoGenerate 1 where not Exists(Key, '$(vFieldname)');
  Next fieldIdx
Next tableIdx
RenameMap:
Mapping LOAD * Resident TempRenameMap;
RENAME Fields Using RenameMap;
Drop Table TempKeys, TempRenameMap;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[kmstephenson]

Thanks Rob, this too is very helpful. Is there any way to programmatically generate a list of synthetic keys (e.g. list of fields used in more than one table) ?  Currently, the logic below works well for the unique field names and if the keys/duplicate field names are renamed prior to those steps and added onto TempKeys. I'm trying to remove all manual steps - such as identifying which fields have duplicate table names across tables (some of these are keys and others need to be renamed) as I would like this logic to be able to be implemented for our projects as well. Thanks!!

[Kushal_Chawda]

Did you tried what I suggested for this?

[rwunderlich]

Because RENAME does not allow you to limit to table, I think you will have to do something along the lines of what @Kushal_Chawda is proposing.  Alias or Qualify at least the common fields up front -- or through an additional load. 

-Rob

[kmstephenson]

So I did try a combination of ALIAS or renaming those duplicate fields prior and then excluding those from the rename process. Is there any way to programmatically generate that list of duplicate field names across tables so that the dashboard developer is aware of which fields need to be addressed? For example, is there a WHERE clause on the below that I could use to check for field names duplicated? Thanks so much! 

for i=0 to NoOfTables()             //DO NOT CHANGE
let vTableNameCheckDups = TableName($(i));
for j=1 to NoOfFields('$(vTableNameCheckDups)')
let vFieldNameCheckDups = FieldName($(j),'$(vTableNameCheckDups)');
TempCheckDups:               
LOAD '$(vFieldNameCheckDups)' as CheckDups
AutoGenerate 1;
next
next

[Brett_Bleess]

Going way out on a limb on this one, Rob may laugh at me, but have a look at the following Design Blog post, may be the piece you need to get things more automated, but I am not sure, Rob generally covers all the avenues, so not really sure on this one.

https://community.qlik.com/t5/Qlik-Design-Blog/Preceding-Load/ba-p/1469534

Regards,
Brett

[Zaga_69]

Hi Rob,

I tried to reuse your code for replacing all blank spaces between field names. For example:

"xxx xx"

"xxxx xxx"

all my field names have blank spaces.

I modified the code as follows but it did not work:

For fieldIdx = 1 to NoOfFields('TempTable')
LET vFieldname =FieldName($(fieldIdx), 'TempTable');
LET vFieldnameModified =Replace(FieldName($(fieldIdx), 'TempTable'),' ','_');
RENAME Field [$(vFieldname)] TO $(vFieldnameModified);
Next fieldIdx

however line 3 is not working, because it is not replacing the blank spaces,

Any idea?

Thanks!

[Zaga_69]

even by doing the following modification, it is not working

For fieldIdx = 1 to NoOfFields('TempTable')
LET vFieldname =FieldName($(fieldIdx), 'TempTable');
LET vFieldnameModified =Replace($(vFieldname),' ','_');
RENAME Field [$(vFieldname)] TO $(vFieldnameModified);
Next fieldIdx

[rwunderlich]

I believe you need single quotes around the Fieldname in Replace:

LET vFieldnameModified =Replace('$(vFieldname)', ' ' ,'_');

Without that you are doing a replace against the value of the field, not the field name. 

-Rob