topic Re: [best practice] how to decode numeric values into intelligible strings in QlikView

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

Thu, 07 Mar 2013 11:44:11 GMT

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!

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

alexandros17 — Thu, 07 Mar 2013 12:12:03 GMT

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

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

jonathandienst — Thu, 07 Mar 2013 13:04:19 GMT

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

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

hic — Fri, 08 Mar 2013 08:29:11 GMT

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

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

Wed, 17 Apr 2013 11:58:06 GMT

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!