[best practice] how to decode numeric values into intelligible strings

[#1]

Dear all,

I have a table in AS400 which is carrying a hefty number of fields populated by numeric values (usually no more than 4).

Those numeric values have of course a meaning behidn it, and here I am asking to you what is the best practice to accomplish that?

What I can think of is:

A. LOAD INLINE

creating x INLINE tables as the x fields that need this translation

B. IF in the script

in the LOAD script of this AS400 table, writing the script as (if(field=1,'apple', if(field=2, 'banana', 'mango'))

C. IF in the QVW

Simply, in the UI lsit box or table, I input there the IF statement to "translate" the numeric values

What is the best approach? and further, are there some other approaches?

Thank you very much for your opinions!

[alexandros17]

I can tell you the worst practice is using IF in expressions or dimensions, I should use the B) solution (but even the A) is good .... take even a look to the keyword MAP that is very useful in those cases.

Hopeit helps

[jonathandienst · Accepted solution]

Hi

The best practice for this scenario is to create mapping tables (containing two fields, the code and the description). This can be done 3 ways:

• From your system database (if the mappings exist in the database). This has the advantage of no additional maintenance, but depends on the system.
• From an external source, such as Excel files. These mappings could be maintained by a data administrator.
• From inline tables. Simplest, but maintenance implies fiddling with the load script and could break the document.

Use Mapping Load.... statements into named mapping tables, one for each mapping, eg:

     MapItemType:

     Mapping Load ItemTypeID, ItemTypeName

     From ItemTypes;

Then use ApplyMap during your load to bring the descriptions in:

     Items:

     LOAD ItemID,

          ...

          ApplyMap('MapItemType', ItemTypeID) As ItemType,

          ItemTypeID,

          ...

     From Items;

You probably dont actually need the ItemTypeID after you have loaded, in which case you can omit the ItemTypeID line. Now the table contains the ItemType Descriptions.

Hope that helps

Jonathan

[hic]

I would do it exactly like Jonathan Dienst describes it. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

HIC

[#5]

Sorry for the awful delay in asnwering and indeed thank you for the replies of you all, much appreciated!

Hinestly, I still do not understand what would be the difference between a "simple" solution like

names:
LOAD * INLINE [
    SCONL, Names
AA, Warehouse 1
AB, Warehouse 2
];

and going through the ApplyMap function, linked to an inline table.

Is it just a matter of "script developing etiquette"? Or are there real advantages connected to it?

Many thanks again for your support!