Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
kmstephenson
Creator
Creator

Rename all fields programmatically from each table in script

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!

21 Replies
kmstephenson
Creator
Creator
Author

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

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

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
Employee
Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Zaga_69
Creator
Creator

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

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