Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES

Renaming fields in the script is something that all QlikView developers do, not only for creating links between tables, but also for making the sometimes cryptic database field names understandable for the users. So how do you best do that? The question seems trivial, but there are in fact a number of things to say about renaming fields.

First of all, the most common way is to use aliases inside the Load or SELECT statements. If there are  many fields to rename you may not want to do this, since it seems like tedious work having to enter all new field names manually in the script. But doing it has the advantage that it makes the script easy to understand for other developers.

 

CompanyName as CustomerName,

 

A second way is to use an Alias statement or a Qualify statement before the Load. These two statements are similar, in that they “silently” rename the fields in the output of the Load statement.

 

Alias CompanyName as CustomerName;
Load * From Customers;

 

However, a consequence of using the Alias or Qualify statement is that you cannot use a resident load that refers to an original field name – instead it must refer to the field name as defined in the Alias/Qualify statement. This is sometimes confusing – especially if the Alias statement is written earlier in the script, far from the Load statement. The Alias and Qualify statements will certainly make your script harder to understand for other developers.

 

A third way to rename fields is to use the Rename statement. This is a very good method if you want to rename all or some fields at the end of the script run: The best way is to have a mapping table with the old and new field names and use this as follows:

 

FieldNameMap:
Mapping Load OldFieldName, NewFieldName From FieldNames ;
Rename Fields using FieldNameMap;

 

You can store the mapping table in your database or in an excel sheet so that it is easier to maintain.

 

Field Name Map.png

 

Which methods do I use? I usually use aliases within the Load statements to define the data model. I never or rarely use the Alias or the Qualify statements.

 

In addition, I often end the script with a “Rename fields” statement to make the field names user-friendly. This way the script itself uses the database field names that are understandable for developers - who often are well familiar with the source database - while the app interface (list boxes, current selections, etc.) uses more user-friendly field names that are understandable for the users.

 

The result: I get the advantages of both naming schemes.

 

HIC

51 Comments
MVP & Luminary
MVP & Luminary

Hi Henric,

Rename Fields using FieldNameMap;

is a great feature. I'm using this if I'm facing square brackets in field names - usually in CSV files (e.g. from probes or data loggers).

http://community.qlik.com/docs/DOC-3371

Best regards,

Ralf

7,246 Views
Specialist III
Specialist III

Thanks, Henric. That's clever - I never thought of doing that.

Is there any quick way to get all of your current field names into a single-column Excel file? That'd make it a lot easier to set up a translation file.

0 Likes
7,246 Views

Yes, there is.

  • Create a list box with the field "$Field". Make sure you have checked "Show System Fields".
  • Right-click the list box and choose "Copy to Clipboard -> Possible values"
  • Paste in Excel

Good luck!

HIC

PS

Rename is very useful but has one limitation: You cannot change the data model with it, i.e. you cannot rename two fields so that they get the same name.

7,246 Views
Specialist III
Specialist III

Thanks again.

I was thinking of using it as an end-of-model translation spot. I tend to wind up with a fair number of fields that retain their original DB name, and going over my script to change them (often in multiple queries, as I query a lot of identical DBs) is a hassle. This should provide a much easier mechanism, and it should allow me to pass the translation work on to someone who knows the database better than I do.

0 Likes
7,246 Views

Sounds like "Rename Fields" is exactly what you need.

HIC

0 Likes
7,246 Views
Not applicable

Hi Henric,

Thanks for sharing this.

I also use "Rename Fields" for the renaming in the end of the script. Additionaly I use the "Comment Fields" to attach the original DB-fieldname to the field. Works technically fine when "Comment Fields" follows "Rename Fields", but in this case there are some mix-ups because the "Comment Fields" is based on the new fieldnames.

When on the contrary the "Rename Fields" follows the "Comment Fields" the fields will first be commented, then renamed, but unfortunately after the renaming the comments will be gone... Do you know why and if there is an easy way around?

Best regards,

Thomas

7,246 Views