Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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:
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
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
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:
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
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
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!