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!

[Kushal_Chawda]

What is the use case to do so? Can you please provide example?

[kmstephenson]

For a data exploration application, our end users want to be able to identify which table a field is coming from (given field names are replicated across tables at different levels). I don't want to use QUALIFY because the table names are long and I'd like this to be a part of the field name itself.

[rwunderlich]

This loop will do what you ask. 

For tableIdx = 0 to NoOfTables() - 1
  Let vTableName = TableName($(tableIdx));
  Let vPrefix = left('$(vTableName)', 2) & '_';
  For fieldIdx = 1 to NoOfFields('$(vTableName)')
    LET vFieldname = FieldName($(fieldIdx), '$(vTableName)');
    RENAME Field [$(vFieldname)] TO [$(vPrefix)$(vFieldname)];
  Next fieldIdx
Next tableIdx

But it will make the key names constant -- with multiple prefixes.  Is that what you want or do you need to break the links?

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

[kmstephenson]

Hi Rob,

I would like to break the links for all but the key fields. Is it possible to do a loop just for the renames needed within each separate table?

E.g.

TableABC:

LOAD Key1,

            Key2,

[INSERT LOOP]

            XX_VARNAME1,

            XX_VARNAME2,

            ...

Note, the XX_ prefix will be assigned by me at a table level - it will not be the first two characters of the actual table name.

Thanks for the help!

[Kushal_Chawda]

If you want to provide the table prefix of our choice then I have slightly modified what @rwunderlich  has suggested.

You need to manually defined the Key field in wildmatch condition to break links

Let's assume you have below 3 tables

TableA:
LOAD * Inline [
ID,Sales
1,100
2,200 ];

TableB:
LOAD * Inline [
ID,Name
1,a
2,b ];

TableC:
LOAD * Inline [
ID,Order
1,11
2,22 ];

for i=0 to NoOfTables()-1

let vTableName = TableName($(i));

Table: // define prefix for each table. Make sure that Table name should match
LOAD * Inline [
TableName,TablePrefix
TableA,A
TableB,B
TableC,C ];

TablePrefix:
LOAD TablePrefix&'_' as TablePrefix
Resident Table
Where TableName='$(vTableName)';

LET vTablePrefix = Peek('TablePrefix',0,'TablePrefix');

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

let vFieldName = FieldName($(j),'$(vTableName)');

let vMatch = not wildmatch('$(vFieldName)','*ID*'); // check key field condition. Do not rename to key field

if '$(vMatch)' = -1 then

RENAME Field [$(vFieldName)] to [$(vTablePrefix)$(vFieldName)];

ENDIF

next

DROP Table TablePrefix;

next

DROP Table Table;

[Kushal_Chawda]

another approach

for i=0 to NoOfTables()-1

let vTableName = TableName($(i));

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

let vFieldName = FieldName($(j),'$(vTableName)');

Fields:
LOAD '$(vTableName)' as TableName,
     '$(vFieldName)' as FieldName
AutoGenerate 1;

next

next

Left Join(Fields) 
// define prefix for each table. Make sure that Table name should match
LOAD * Inline [
TableName,TablePrefix
TableA,A
TableB,B
TableC,C ];

//Difine the fields which you don't want to Rename
NoRename:
LOAD Concat(DISTINCT chr(39)&Fields&chr(39),',') as NoRename_Fields;
LOAD * Inline [
Fields
ID
Order2 ];

let vNoRenameFields = Peek('NoRename_Fields',0,'NoRename');

Map_RenameFields:
mapping LOAD FieldName as RenameFrom,
     TablePrefix&'_'&FieldName as RenameTo
Resident Fields
where not match(FieldName,$(vNoRenameFields));

RENAME Fields using Map_RenameFields;

DROP Tables Fields,NoRename;

[kmstephenson]

This is very close - only problem is that some of the fields with the same field names across tables are keys and others are not keys and should be named with the table prefix. It looks like the RENAME statement is working for the links/keys between table (as these are individually listed in the load * inline to be excluded). However, for duplicate field names across tables that are not keys, it looks like these are all being renamed with the table prefix of the first table loaded with these variables. Does that make sense? How do I fix this?

[Kushal_Chawda]

Understood your point and that is why Qlik has Qualify concepts. Using Qualify you can easily rename the fields (Just change the table name to prefix you want). This is the only drawback of dynamic renaming that Rename field fucntion does not refer to table  Unfortunately we can't do like Rename field ID to A_ID where Tablename 'A'   otherwise your query would have solved now.

In your case common field names which are not key, you need to alias them manually and add those renamed field to mapping table so that Dynamic rename function will not rename that field again.

Here, Order is common between TableB & TableC which you want to rename
TableA:
LOAD * Inline [
ID,Sales
1,100
2,200 ];

ALIAS Order as B_Order;
TableB:
LOAD * Inline [
ID,Name,Order
1,a,12
2,b ,13];

ALIAS Order as C_Order;
TableC:
LOAD * Inline [
ID,Order,Order2
1,11,111
2,22,111 ];

for i=0 to NoOfTables()-1

let vTableName = TableName($(i));


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

let vFieldName = FieldName($(j),'$(vTableName)');

Fields:
LOAD '$(vTableName)' as TableName,
     '$(vFieldName)' as FieldName
AutoGenerate 1;

next

next

Left Join(Fields) 
// define prefix for each table. Make sure that Table name should match
LOAD * Inline [
TableName,TablePrefix
TableA,A
TableB,B
TableC,C ];

//Difine the fields which you don't want to Rename and already renamed via alias keyword
NoRename:
LOAD Concat(DISTINCT chr(39)&Fields&chr(39),',') as NoRename_Fields;
LOAD * Inline [
Fields
ID
Order2
B_Order
C_Order ];

let vNoRenameFields = Peek('NoRename_Fields',0,'NoRename');

Map_RenameFields:
mapping LOAD FieldName as RenameFrom,
     TablePrefix&'_'&FieldName as RenameTo
Resident Fields
where not match(FieldName,$(vNoRenameFields));

RENAME Fields using Map_RenameFields;

DROP Tables Fields,NoRename;

[rwunderlich]

I can suggest some code changes as Kush has, but let me take a step back and ask a couple questions. 

1. Why not name the tables with the short name you want and use QUALIFY * and UNQUALIFY for the keys?

2. If the goal is for the user to understand what table a field is in, can they do that with $Field and $Table?

-Rob