How to Rename Fields

[hic]

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.

[hic]

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.

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

[rbecher]

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

[Or]

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.

[hic]

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.

[Or]

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.

[hic]

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

HIC

[#8]

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

[hic]

This behaviour is obviously not what you expect. I'll discuss it with some people here to have this classified as a defect. Then it might get fixed.

HIC

[#10]

Ok, thanks for your answer and for discussing it!

Would you mind posting the bug ID if it's classified as defect? This would make it easier to check the coming release notes for any fixes.

Thomas

[hic]

Meanwhile I just thought of an obvious work-around: Store the comments in the same table as the new field names. Then end the script with

NewFieldNames:

Mapping Load OldFieldName, NewFieldName From FieldNameTable;

NewFieldNameComments:

Mapping Load NewFieldName, FieldNameComment From FieldNameTable;

Rename fields using NewFieldNames;

Comment fields using NewFieldNameComments;

HIC

[#12]

This is figuratively how my script works at the moment.

Unfortunately NewFieldName (SCRTEXT_L) is not yet distinct. This means when the comment is done it takes not necessarily the correct NewFieldNameComment.

For Illustration:

Because in the data load out of SAP there is the same fieldname for different OldFieldName (FIELDNAME_ORIGINAL) I manually loaded at first (load order) a dedicated new fieldname 'Vorschlagskontierung Auftrag' for 'CSKB.AUFNR'. All I have to do to get the work-around to work is get rid of the duplicated (in the example the second) row. But unfortunately I can't figure out how to do.

A load distinct is not possible because just the fields TABNAME, FIELDNAME,  FIELDNAME_ORIGINAL and ROLLNAME have to be distinct. A left join didn't worked either. Perhaps you have the missing link for me.

Sorry for the extended question, but any help is appreciated.

Thanks a lot in any case!

Thomas