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

[GeorgeQV]

Thank you, I will continue this way!

[beck_bakytbek]

Thanks for sharing

[Anonymous]

Hi Henric

I have 2 columns with Primary Owner and Secondary Owner. These are essentially name fields with user names. We need to have one column of names. I have done applymap to the Primary owner field to get a business name and renamed the field to Owner. When i do an applymap with the secondary owner name field i am not able to rename this field to Owner as qv doesn't permit.is there any other way that I can have one common field after mapping the user name's from both these fields to one standard name field?

[hic]

In other words, you want two names - i.e. two records - associated with each record in the data table. Then you need to create a canonical owner in a second table and link it to the first table using the RecordID:

[Canonical Owner]:

Load RecordID, [Primary Owner]   as Owner, 'Primary'   as NameType Resident DataTable;

Load RecordID, [Secondary Owner] as Owner, 'Secondary' as NameType Resident DataTable;

See also Canonical Date

[Anonymous]

I am getting synthetic keys while doing this ..I have pasted my sample file . File concatenates the extract from multiple sources i.e. RTC, Problems, Incidents and TR.

A mapping sheet has been created as the names are different in each of these extracts so as to map these to the actual names that are required.

In the RTC extract there are 2 fields i.e. Primary and Secondary Owner against one ticket. So each ticket is resolved by 2 people.  I have to put a list box with the names so when the user selects on a particular name the count of the tickets resolved by that person is calculated irrespective of the name appearing in the primary or secondary column should appear. The count should take into consideration both the cols i.e.Primary and Secondary.

[Anonymous]

Is there a way to share my file with you so that you may able to suggest a workaround?  I am not able to attach my file in the response..

[Anonymous]

Thanks Henric.. I was successfully able to generate a canonical owner.. I followed the above steps..I had to remove the Owner from other loads and load them again in similar fashion

[santiago_respane]

Excellent post! Thanks a lot!

[Romina]

Buenas Noches,

Necesito su ayuda, tengo 2 bases en el Qlik que comparten varios nombres de campos, quiero cambiar los nombres solo de una de las tablas en Qlik, pero usando Mapping me cambia los nombres de los campos de ambas tablas.

¿Como es posible indicar que se cambien solo los nombres de una de las tablas?

Yo utilice para cambiar los nombres:

FieldMap:
Mapping
LOAD * Inline [

];

y luego Rename Fields using FieldMap;

Desde ya muchísimas gracias!

Saludos

[hectorgarcia]

Romina:

Renaming Fields for individual tables, I recommend that you use 
CompanyName as CustomerName as advised by Henric.

And then use the Rename Fields using FieldMap for the rest of the fields.

Saludos

Héctor