Mindmajix

How to Rename Fields using Mapping Table

How to Rename Fields using Mapping Table

Renaming fields in the script are something that all QlikView developers do, not only for creating links between tables, but also for making the cryptic database field names understandable for the users.

Creating user –friendly data field name is the key to help both developers and end users equally in deciphering the qlikview application. As discussed earlier, you can use renaming techniques on non-key fields such as

  • AS: It makes the script easy to understand for other developers.
  • RENAME: This is the best method if you want to rename all or some fields at the end of the script run. This saves countless hours, allowing you to focus more on scripting an efficient data model into QlikView and working on front-end development of data visualization objects.
  • ALIAS: Common way is to use aliases inside the load statements.

But an easier, more consolidated, and easy-to-update method is to use a mapping table. This file may be stored as an external file for quick editing, and is easier for developers consult and understand.

The main idea of a mapping table is to provide a two-column list of the existing field names and desired field names. The mapping table, in this case, will be an external. XLS excel file. You can also use an inline table, if desired.

To create a list of the existing data field names in your script, you can either comb over your script carefully or accomplish it with a very easy method: on the sheet in your qlikview application, right-click on a blank area, click on select fields from the menu, and then click on ok. choose to show system fields, then move $field to the right window to select it (be careful so as not to choose $fields-plural). A list box displays all the existing field names. Make sure you clear all the selections in the document (all field names in the list box should have a white background) and right-click on the list. Go to copy to clipboard possible values. Open excel, label the first column (cell A1) as existing, and then paste the values into that column (cell A2). In the next column (cell B1), enter desired, and then enter your desired data field names, corresponding to each of the source data names.

Save the excel file as mapping.XLS to your desired location (in this example, we will use the location as c:\users\external), and make a note of the location for reference in the script later. In the script, you can also add the following code:

Mapping Table:

MAPPING LOAD

      Existing,

  Desired

FROM

C:\users\external\mapping.XLS

(biff,  embedded  labels, table

Is   [sheet 1 $])

WHERE len (desired)>0;

The WHERE clause in the preceding code instructs qlikview to only look for non-null values in the desired field using the len>0 (character length being greater than zero) function.

We can now instruct qlikview to use the new table called mapping table. Add the following code to the end of the script after the MAPPING LOAD block of code is discussed, as shown in the following code:

RENAME FIELDS USING

Mapping table;

Note that all field names that do not have a data field name under the existing column in the mapping table will not be renamed.

Enroll for Instructor Led Live Qlikview Training 

0 Responses on How to Rename Fields using Mapping Table"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.