Rename all fields programmatically from each table in script
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)?
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 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!!
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.
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
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.
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.
I tried to reuse your code for replacing all blank spaces between field names. For example:
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,
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