mapping load

[sekharapp]

why should we need to go mapping load rather than  joins

is there any other reason for mapping load other than two or three common field in table?

what are the limitations of mapping load?

[vikasmahajan]

What is QlikView Mapping?

In simplest of words, QlikView Mapping is temporarily making a table (mapping table) using data or field values from pre-existing tables from different models and sources. mapping load create temp tables in memory.

// Load mapping table of country codes:

MapCountry:

mapping LOAD *

Inline [

CountryCode, Country

Sw, Sweden

Ind, India

Chn, China

Ity, Italy,

Cnd, Canada

Dk, Denmark

No, Norway

];

StoreManagers:

LOAD *,

ApplyMap('MapCountry', CountryCode,'Others') As Country

Inline [

CountryCode, StoreManager

Sw, John Dalton

Cnd, Mary Robins

Ity, Andrea Russo

Arg, Harry Gibson

Dk, William Gilbert

Ind, Indrani Sen

No, Daniel Larsen

Rom, Emilia Mark];

// We don't need the CountryCode anymore

Drop Field 'CountryCode';

The Mapping load only takes two fields in the table. However Join can be used if you want to select a result set of the source tables. Joins can work on more than one Field and their values with four types(outer, inner,left and right).

Hope this clear you.

Thanks

vikas

[HugoRomeira_PT]

Hello,

Take a look at this link (it explains the differences): https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/use-map...

The Mapping is more similar to a VLOOKUP option. Meaning you should use it when you are interested in looking up for a single value. 
With the join you will retrieve as many combinations as the ones that exist, which can sometimes leads to duplications, or performance issues.

In any case, these functions are both usefull in their own scenario.

Hope it helps.

Best regards

Hugo Romeira

[vchuprina · Accepted solution]

Hi,

Advantages:

• Mapping load works faster than joining
• In the mapping load, you can add hardcoded values if you don’t have values in the mapping table
• With mapping load you can add fields and make a necessary transformation in the same table load, you shouldn’t make an additional resident load.
• Mapping table removed by Qlik automatically after reload

Disadvantage:

• The mapping table contains only two fields, so you need to create a separate mapping table for each field.
• In the mapping table, you should have a one-to-one relationship between Key and Value. In case you have two values per key you will lose the second value

LEFT JOIN:

ProductTMP:
LOAD * Inline[
Product, Qty
Apple, 10
Banana, 4
Orange, 7
Strawberry, 5
];

Left Join(ProductTMP)
Price:
LOAD * Inline[
Product, Price, Cost
Apple,  5, 3
Banana, 3, 1
Orange, 4, 2
];

//Use resident load to calculate Price and Cost amount
Product:
LOAD
     Product,
     Qty,
     Price,
     Cost,
     Qty * Price AS [Price Amount],
     Qty * Cost  AS [Cost Amount]
Resident ProductTMP;

Mapping Load:

Price:
LOAD * Inline[
Product, Price, Cost
Apple,  5, 3
Banana, 3, 1
Orange, 4, 2
];

//Create Separate mapping table for Price and Cost
PriceMap:
Mapping LOAD
     Product,
     Price
Resident Price;

CostMap:
Mapping LOAD
     Product,
     Cost
Resident Price;

DROP Table Price;

//Calculate Price Amount and Cost Amount during Product table load
Product:
LOAD
     *,
     Qty * Price AS [Price Amount],
     Qty * Cost  AS [Cost Amount];
LOAD
     *,
     ApplyMap('PriceMap', Product, 'Not Available') AS Price, //Add Not Available for missing price
     ApplyMap('CostMap',  Product, 'Not Available') AS Cost; //Add Not Available for missing cost
LOAD * Inline[
Product, Qty
Apple, 10
Banana, 4
Orange, 7
Strawberry, 5
];

Regards,

Vitalii