Thursday, 9 May 2013

User friendly field names in QlikView

I spent a day last week working with a customer planning some improvements to their solution and taught them a few little tricks on the way. One tip that came up in conversation happened to be one of my favourite little QlikView tricks (yes I know I need to get out more often) and so I thought I'd share it here too.

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:

Cusomters:
LOAD
    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 names

First 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 names

Unfortunately 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 table

We can now go back to our QlikView document and add our field name mapping table to the very end of our script like this:

FieldMap:
MAPPING LOAD
    ExistingFieldName,
    UserFriendlyFieldName
FROM ....
WHERE len(UserFriendlyFieldName)>0;


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 map

Applying 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!!! 

12 comments:

  1. Oh my God...where was this tip 2 years ago when I started?!? Man, now what do I do? I have a bunch of existing apps I'd like to clean up, but don't want to break a ton of things down the line...or break any custom created user objects.

    This ALWAYS bugged me, but I always wanted my fields to have friendly names. It annoyed me that if you didn't change the field names, but re-labeled them in a table for example, when selected, it still showed the database or script name in Current selections.

    Why doesn't Qlikview just give us the option of displaying "label names" in the current selections? That would help a ton.

    Thanks for the tip, I'll use it going forward at least.

    ReplyDelete
  2. Hi Nate

    Thanks for the comment. Give it a try on an existing app, you might be surprised how much still works. QlikView is pretty clever about applying the field name changes through out objects and even expressions. It doesn't always get it right but it does most of the time.

    Regards
    Matt

    ReplyDelete
  3. Excellent article. I use the same approach. You can use a similar statement to also add comments to the fields: COMMENT FIELDS USING [Mapname]. If you put all that information in the same spreadsheet, you can use that as your documentation (or use it to generate your documentation).

    ReplyDelete
  4. This is excellent. I would like to offer another approach. Create a semantic QVD layer instead of creating mapping. And consume that optimized QVD in your application.

    ReplyDelete
    Replies
    1. Hi Shilpan
      A QVD layer is generally considered best practice in all implementations and doesn't solve the issue. You still need to somehow cleans/map the field names in the QVDs and the above approach can still be used to do this.

      Regards
      Matt

      Delete
  5. well, it is a good tip but there is a huge downside. Developers like to see all the code and data fields where they came from and with this approach, you have to go to that excel document to see your original field names. You also have to maintain now your column names in two places - in your load script and in that mapping doc.

    So for me, aliases with AS in a LOAD script is still a winner.

    ReplyDelete
    Replies
    1. Indeed, there is no perfect solution, each has its own pros and cons. Unfortunately ease of use for the end user tends to trump ease of use for the developers.

      As mentioned by Barry above, you could use the same approach to add a comment to the field including the original data source field name. That way you'd still be able to see the link between a field in the UI and your QV script without having to paw through the mapping spreadsheet.

      Regards
      Matt

      Delete
  6. Thank you - this is a superb tip!

    ReplyDelete
    Replies
    1. Thanks Meghan, glad you find it useful!

      Delete
    2. Thanks for the info, but I have a question: I tried to do the mapping using a resident table (to avoid using an external file). QV mapped the fields correctly, but none of the expressions or objects updated the old field to the new one. I have several charts and lists and after the mapping, none of them showed values, because the source fields were unavailable.

      Do you know how can I overcome this?

      Many thanks, Matthew!

      Delete
  7. Hi,

    Whether QlikView automatically updates your sheet objects it a bit hit and miss. If it doesn't then you have to change them manually I'm afraid. You can use the find and replace options under "Settings"->"Expression Overview..." to speed up changing all expressions.

    Regards
    Matt

    ReplyDelete
  8. Thanks for giving me hope. However in my case friendly names aren't unique for each field name but are repeating, so the solution doesn't work. Actualy it manages only to rename one tabel. Any hint to resolve my issue?

    ReplyDelete