Qlik Community

QlikView Documents

Documents for QlikView related information.

Missing Manual - Map...using

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(20+ articles)

Labels (3)
Comments
ecolomer
Honored Contributor II

Good.

Thank's for sharing

Saludos

Enrique

gilbertomedeiro
New Contributor III

Hey Robert,

Congratulations! Very easy to understand and pretty useful!

Thank's for sharing your knowledge in Qlikview. I really appreciate!

Regards

Gilberto

vkish16161
Contributor III

Robert,

How is this different than using a Mapsubstring function?

Just curious.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-07-03 07:47 AM
Updated by: