Benefits of Concatenating a Code and Description

In the typical business report in a corporate environment you need to include quite a few selections either through a list box or multi-box. There may be selections for product, customer, sales executive, division, channel, product type, country, fiscal period, etc. You probably have codes for those things in your corporate databases and, if you’re lucky, you also have descriptions that go along with the codes.

When we build a new QlikView report we design the loadscript to load not just the codes but also the concatenation of the code and the corresponding description separated by a hyphen. That part of the loadscript code might look like this:
PRODUCT_MASTER:
SQL SELECT
  PRODUCT_CODE,
  PRODUCT_CODE||' - '||DESCRIPTION AS PRODUCT
FROM PRODUCT_MASTER;

Then, we use the PRODUCT field in list boxes, multi-boxes and in the chart dimensions. The reason this idea is so valuable is because of QlikView’s behavior when you are making a selection: if you click on the title bar of a list box or on the down arrow of a multi-box and then start typing, the visible list of items changes to show only the ones that match what you have typed so far.

For example, if you click the down-arrow on the multi-box selection for our example field, PRODUCT; and then start typing baby -- all of the products that contain the word “baby” in the product description move to the top of the list high-lighted with a white background. Keep typing, baby lotion… and now only the products whose names contains the words “baby lotion” are on the top of the list. At that point you can either press the Enter key which will select all of the high-lighted products or you can click on an individual product with your mouse. If that field only contained product codes then you would be limited to typing in a subset of the product code – which you can still do, of course, with the concatenated field values but the extra value of being able to type in a partial description to help with the selection is extremely useful.

A description for a code value is so useful that we have sometimes loaded the description data from a spreadsheet when it isn’t available on the corporate database. Then it is joined and concatenated to the code value in the loadscript.

Using the concatenated code and description as a dimension in the charts also works well. The field still sorts by product code since that is the leftmost part of the field value. Often we shorten the field width so that just the product code is showing on the screen but moving your cursor over the field reveals the complete description.