Rename Field and Qualify in Qlikview

Qlikview Rename Field, Qlikview Qualify 

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

If you would like to become a Qlik Sense Certified professional, then visit Mindmajix - A Global online training platform: " Qlik Sense Certification Training Course ". This course will help you to achieve excellence in this domain.

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:

Load ID as ClientID, Name, Address, Postal Code, City, State from Clients.csv;

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.

Frequently Asked QlikView Interview Questions & Answers

The Rename Field 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:

Map_Table: Mapping LOAD DATA_FIELD_NAME, DISPLAY_FIELD_NAME FROM [.. QV mapping_names.xls] (biff, embedded labels, table is Sheet1 $); Rename Fields using Map_Table;

 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:

Related Page: How To Concatenate Two Tables In QlikView

The QUALIFY statements:

The QUALIFY statement is used to accomplish ‘full naming’ in the format table name.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:

QUALIFY ID, Name;
 [Customers]:
 LOAD
ID,
Name
FROM
C: Samples Customers.qvd
(qvd);
UNQUALIFY *;

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:

Customers:
Load ID, Name
From C: Qlikview Clients.qvd (qvd);
Products:
Load ID, Name
from C: Qlikview Products.qvd (qvd);

And here’s an example using QUALIFY, in which we qualify the ID field and then concatenate in Qlikview:

QUALIFY ID;
Customers:
Load ID, Name
From C: Qlikview Clients.qvd (qvd);
Concatenate Load ID, Name
From C: Qlikview Products.qvd (qvd);
UNQUALIFY *;

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.

QUALIFY “* ID”, Name;
[Customers]:
Load ClientID, Name
From C: Qlikview Clients.qvd (qvd);
Load ShippingID, Name
From C: Qlikview ShipTo.qvd (qvd);
UNQUALIFY *;