Rename Field and Qualify in Qlikview

(5.0)

| 3573 Ratings

To build the association between data fields, QlikView primarily uses field names as the main identifying factor. 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.

There are times when, as a developer, you may want to rename field in order to correctly link or d-link data fields, as in the following examples:

Field linking: If two data fields should be associated but are assigned different names, you can rename the fields with identical names so QlikView treats them as the same. Tables with identically named fields are linked. An important note is that field names are case sensitive in QlikView, and this is critical to field linking/associations.

Field d-linking: If two data fields do not contain the same information, but share identical names, you will need to rename one of the data fields in order to delink or break the association. The tables with the different data field names will then be delinked.

Learn how to use QlikView, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free QlikView Training Demo!

There are a few methods that we can apply to rename data fields: the AS specifier, Alias, Rename Fields and QUALIFY.

The AS specifier:

The AS specifier can be used directly inside a Load statement, such as in the following query:

This method is commonly used because of the ease in using the specifier on a case-by-case basis, directly in the Load statement. In the previous example, the data source field named ID will be renamed as ClientID in the data output. This can be helpful when, for instance, many tables have various ID fields, such as shipper ID or product ID. In this case, we only want IDs associated with clients to be associated with each other, and to break any possible links between tables with any other fields of the name ID.

The Alias statement:

You can also rename incoming data fields by using the Alias statement. This statement precedes the Load statement, as follows:

Alias ID as ClientID;Load * from Clients.Csv;

In this example, incoming data fields from the data source Clients.csv with the name ID are renamed as ClientID. Again, this is useful for establishing or breaking links with tables. The downside of using an Alias statement is that the statement can be used far before the actual Load statement, it affects, which can get lost when reviewing code and debugging by other developers. Another downside is that you cannot use a resident load (a previously loaded QlikView table) that refers to the original data field name— it must refer to the aliased definition of the field name. This can be one confusing aspect regarding the Alias statement.

The Rename Field statement is positioned after the Load statement, and may also use a mapping table for ease of maintenance. The one caveat is that you cannot rename two fields with the same name using the Rename Field statement. If attempted, only the first instance of the renaming will take place; all the other instances will be ignored. Use the Rename Field statement as in the basic example, as follows:

Rename field ID to ClientID;

If you use a mapping table (for example, an Excel file with original and new field names), then the format is:

In this example, you define a mapping table (Map_Table) as the contents of an external Excel spreadsheet (mapping_names.xls). The spreadsheet contains the original data field names and the new display names of the fields you wish to use in the QlikView application. In this example, the contents of the Excel file defining the mapping load are illustrated in the following diagram:

The QUALIFY statement is used to accomplish ‘full naming’ in the format tablename.fieldname (a period, or full stop, separates the table name and field name). Using the Qlikview QUALIFY statement allows you to avoid automatic linking of tables using identical field names, because differing fields will not have identical field names using the QUALIFY statement. The QUALIFY statement can be used as in the following illustration:

The result of the preceding code is that the new Customers table will contain Customers.ID and Customers.Name. The data is extracted from the file Customers.qvd. The UNQUALIFY statement effectively disables any further qualification of data fields coming in after it.

Here’s another example, where QlikView forces auto-concatenation because the field names are the same:

The result of the a fore mentioned Load statement will be a new Customers table with three data fields: Customers.ID, Products.ID, and Name. The two loads are concatenated into one QlikView table (Customers). The Name field has the same name in each table and will not be qualified, since it is not named in the QUALIFY statement.

The wildcard character is useful when using the QUALIFY statement (note the double quotes), as illustrated in the following example. The wildcard can be used when it is impractical to list every field name that must be qualified.

Subscribe For Free Demo

Phone *

E-mail Address *

Free Demo for Corporate & Online Trainings.

About The Author

Vinod Kasipuri writes about various IT platforms such as QlikView, Qlik Sense, and Perl Scripting, at Mindmajix. He loves to explain the concepts he write in simple terms. He is also engaged in researching trends in AngularJS and LabView. Reach out to him via LinkedIn and Twitter.

Mindmajix - Online global training platform connecting individuals with the best trainers around the globe. With the diverse range of courses, Training Materials, Resume formats and On Job Support, we have it all covered to get into IT Career. Instructor Led Training - Made easy.