Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
Map...using
or some kind of a lookup...
In most of the cases we want to keep current field and by mapping corresponding values create new Field.
(Don't join - use Applymap instead)
But sometimes we want to replace data with new one.
Here is where map...using comes in handy..
Description(Qlikview Help)
The map ... using statement is used for mapping a certain field value or expression to the values of a specific mapping table. The mapping table is created through the Mapping statement.
The automatic mapping is done for fields loaded after the map ... using statement until the end of the script or until an unmap statement is encountered.
The mapping is done last in the chain of events leading up to the field being stored in the internal table in QlikView. This means that mapping is not done every time a field name is encountered as part of an expression, but rather when the value is stored under the field name in the internal table. If mapping on the expression level is required, the Applymap() function has to be used instead.
Syntax:
*Fieldlist - is name of the field which value we will be look for
mapname - us the table from where the data will be picked
Replacing matching fields values with corresponding values from the Mapping Table
- Mapping Table must have only two columns
- The fields/tables to be replaced (or map) must be loaded after the map..using statement
Data Model (Example 1)
| Header 1 | Header 2 |
|---|---|
(Copy and Pasted below code into Edit Script window and reload) Cmap: MAPPING LOAD * INLINE [ Code,Country PL,Poland UK,United Kindgdom DE,Germany ]; Map Country Using Cmap; Data: LOAD * INLINE [ Country PL GB DE FR ]; |
|
The outcome is as below:
PL - have been replaced by Poland
DE - have been replaced by Germany
FR and GB - does not have a match in Cmap table
Data Model (Example 2 & 3)
X:
MAPPING LOAD * INLINE [
Code,Country
PL,Poland
UK,United Kindgdom
DE,Germany
];
map A, B, C using X;
Data:
LOAD * INLINE [
A, B ,C
PL, GB,DE
GB, PL,FR
DE, FR,PL
FR, DE,GB
];
Outcome:
In this example matching values from all three fields have been replaced by corresponding values.
The A,B,C field names can replaced by * (Example 3) to get the same outcome
map * using X;
If you wonder where this statement may be useful , think of a model with more than one table where values have to be replaced
In below example all fields that have been stated as map criteria (A,B,C,G - no matter which table ) have been replaced by corresponding values:
| Header 1 | Header 2 |
|---|---|
X: MAPPING LOAD * INLINE [ Code,Country PL,Poland UK,United Kindgdom DE,Germany ]; map A,B,C,G using X; Data: LOAD * INLINE [ A, B ,C PL, GB,DE GB, PL,FR DE, FR,PL FR, DE,GB ]; Data2: LOAD * INLINE [ E, F ,G PL, GB,DE GB, PL,FR DE, FR,PL FR, DE,GB ]; |  |
Feeling Qlikngry?
