Having "clean", user friendly field names is a key aspect of any polished QlikView solution. Even if you disable your users ability to create their own custom objects in the browser, they can still see the often unfathomable field names within the underlying data model when they view their current selections. Even if you don't put a current selections object in your application, it is available as a floating window on the toolbar for both the IE plugin and the AJAX zero footprint so users will inevitably see them at some point.
To solve this problem, you could boviously spend many hours working your way through your script(s) renaming all the fields using AS like this:
CusID AS [Customer ID],
CusName AS [Customer Name],
CusCouCode AS [Customer Country Code],
This would of course work and is a perfectly valid approach, but it can be complicated when your scripts become complex. For example, renaming a field in table which is then the source of a later resident load will mean you have to cascade the new field name throughout everywhere it is used later in the script. There has to be a better, faster way right? Thankfully there is, although don't get too excited, it's no miracle and you'll still have to type in all the user friendly field names. QlikView is good, but it can't read your mind!
Step 1 - Getting our list of existing field namesFirst things first, we need to generate a list of our existing field names within our data model. This is the easy part, after every reload, QlikView creates a set of "System Fields" which exist within the data model but are by default hidden and are not viewable within the Table Viewer dialog.
Open your application and right click on the blank space within on of your tabs. Click "Select Fields..." from the pop up menu and a familiar dialog should open offering the fields within your data model for you to choose from. At the bottom left is a check box labeled "Show System Fields" which you should make sure if checked.
The Available Fields list will now show 6 extra fields all starting with a dollar ($) indicating they are system fields. The one we are interested in is "$Field" (note it is singular, don't confuse it with $Fields which is very different). Add the "$Field" field to your tab and click on "OK".
You can now right click the resulting list box and send it to Excel so that we can create a table to map our existing field names to clean names.
Step 2 - Create our clean field namesUnfortunately there is simply no cheat for this part, you are going to have to type away and create a set of user friendly field names for each field within your data model.
As you can see, I've also added a column title for the existing and user friendly field names to make them more meaningful when I load them into my script later on. If you don't want to relabel a field you can either leave it's "UserFriendlyFieldName" blank or set it to the same at the "ExistingFieldName".
Once complete you can save the spreadsheet and close Excel.
Step 3 - Load the field name mapping tableWe can now go back to our QlikView document and add our field name mapping table to the very end of our script like this:
As with any MAPPING LOAD statement in QlikView, the table should have only 2 fields and the order of the fields is important not their names. The first field in our MAPPING LOAD should contain the existing "dirty" field names and the second field our "clean" user friendly names. Note also the where clause which will discount any records in our spreadsheet for which we've not set a user friendly field name.
Step 4 - Applying the field name mapApplying our Mapping table to the fields couldn't be easier and takes just a single line of script:
RENAME FIELDS USING FieldMap;
This line should appear at the end of your script and must be after you have loaded the FieldMap mapping table as above (Step 3). If a field name is found in the script but doesn't have an entry in my FieldMap table then it will not be renamed.
Now run your script and see the results. Your field names should have been mapped to their new clean names.
And that's it, a pretty simple trick that can make your app a lot more user friendly, and as with all BI useability is everything!!!