topic Re: Rename all fields programmatically from each table in script in QlikView

Rename all fields programmatically from each table in script

kmstephenson — Fri, 10 Jan 2020 15:54:35 GMT

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!

Re: Rename all fields programmatically from each table in script

Kushal_Chawda — Fri, 10 Jan 2020 15:59:49 GMT

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

Re: Rename all fields programmatically from each table in script

kmstephenson — Fri, 10 Jan 2020 16:07:08 GMT

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.

Re: Rename all fields programmatically from each table in script

rwunderlich — Fri, 10 Jan 2020 19:35:18 GMT

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

Re: Rename all fields programmatically from each table in script

kmstephenson — Fri, 10 Jan 2020 20:37:28 GMT

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!

Re: Rename all fields programmatically from each table in script

Kushal_Chawda — Fri, 10 Jan 2020 21:08:26 GMT

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;

Re: Rename all fields programmatically from each table in script

Kushal_Chawda — Fri, 10 Jan 2020 21:28:43 GMT

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;

Re: Rename all fields programmatically from each table in script

kmstephenson — Fri, 10 Jan 2020 22:26:38 GMT

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?

Re: Rename all fields programmatically from each table in script

Kushal_Chawda — Fri, 10 Jan 2020 23:16:26 GMT

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;

Re: Rename all fields programmatically from each table in script

rwunderlich — Fri, 10 Jan 2020 23:31:32 GMT

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

Re: Rename all fields programmatically from each table in script

kmstephenson — Wed, 15 Jan 2020 17:13:22 GMT

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!

Re: Rename all fields programmatically from each table in script

rwunderlich — Wed, 15 Jan 2020 21:08:45 GMT

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

Re: Rename all fields programmatically from each table in script

kmstephenson — Thu, 16 Jan 2020 14:43:54 GMT

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

Re: Rename all fields programmatically from each table in script

Kushal_Chawda — Thu, 16 Jan 2020 15:31:51 GMT

Did you tried what I suggested for this?

Re: Rename all fields programmatically from each table in script

rwunderlich — Fri, 17 Jan 2020 01:39:17 GMT

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

Re: Rename all fields programmatically from each table in script

kmstephenson — Fri, 17 Jan 2020 15:14:18 GMT

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

Re: Rename all fields programmatically from each table in script

Brett_Bleess — Mon, 10 Feb 2020 22:04:53 GMT

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

Re: Rename all fields programmatically from each table in script

Zaga_69 — Thu, 19 Nov 2020 16:18:51 GMT

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!

Re: Rename all fields programmatically from each table in script

Zaga_69 — Thu, 19 Nov 2020 16:32:08 GMT

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

Re: Rename all fields programmatically from each table in script

rwunderlich — Thu, 19 Nov 2020 23:01:19 GMT

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