Missing Manual - Map...using

              1f4fe19.jpg

        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..

     


    mapa.jpg

    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:

    synax.jpg

    *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 1Header 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

    ];

    E1.jpg

     

    The outcome is as below:

    table 1.jpg

     

    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:

     

    Table2.jpg

     

    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;


    Table 3.jpg



    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 1Header 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

    ];

    tab4.jpg




    Feeling Qlikngry?


    How To /Missing Manual(18+ articles)