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:






    (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:


    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. Check out QlikView here.

    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:


    Source: Mindmajix