Missing Manual - Map...using

robert_mika — Tue, 20 Sep 2022 17:53:53 GMT

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 ];

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

];

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)

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 ];

Header 1

Header 2

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?

How To /Missing Manual(20+ articles)

Re: Missing Manual - Map...using

ecolomer — Sun, 13 Mar 2016 06:12:53 GMT

Good.

Thank's for sharing

Saludos

Enrique

Re: Missing Manual - Map...using

gilbertomedeiro — Thu, 11 Aug 2016 20:19:08 GMT

Hey Robert,

Congratulations! Very easy to understand and pretty useful!

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

Regards

Gilberto

Re: Missing Manual - Map...using

vkish16161 — Wed, 15 Nov 2017 18:46:14 GMT

Robert,

How is this different than using a Mapsubstring function?

Just curious.

article Missing Manual - Map...using in Member Articles

Missing Manual - Map...using

Re: Missing Manual - Map...using

Re: Missing Manual - Map...using

Re: Missing Manual - Map...using