topic What is a mapping table? in QlikView

What is a mapping table?

Anonymous — Mon, 05 Mar 2012 16:29:48 GMT

Hi,

Could someone explain what a mapping table is with an example, is it link table ?

Thanks

Re: What is a mapping table?

magavi_framsteg — Mon, 05 Mar 2012 16:47:01 GMT

Hi Balraj.

It's sort of a join and behaves like a function.

MyMap1:

mapping load idValue, textValue;

tab1:

load field1, field2, applymap('MyMap1', field3, 'value if map does not find field3 in map table');

field3 is sent as a parameter to the applymap call.

field3 is checked against MyMap1 and the corresponding textValue is returned. If no match it gets the third parameter of the applymap call.

Check the QV Reference manual for a complete manual of howto use maps.

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

What is a mapping table?

Mon, 05 Mar 2012 18:00:01 GMT

Hi Balraj,

A mapping table is used for what is called a 'star' model (see bellow an example).

This kind of mapping table is used to avoid synchronization issues. The Key table contain all concatanations that is used to link the tables. then, each table is linked to the others via the mapping table.

Hope this helps

Re: What is a mapping table?

jonathandienst — Mon, 05 Mar 2012 19:11:40 GMT

Balraj

Let's look at a typical example. Let's say that we have a table with a product code and product name, and we have a sales table that contains the product code. And we want to bring the product code into the sales fact table:

//Get the product code/names from some source. The field names are unimportant, but there should

Products:

LOAD * Inline

[

Code, Name

A1, Widget

B2, Dingbat

];

//Create a mapping table for the product code/names.

//The field names are unimportant, but there should always be two fields.

MapProducts:

Mapping LOAD * Resident Products;

//Load the sales data. Applymap brings the product name in from the mapping table,

//and uses 'UNKOWN' if the code is not found

Sales:

LOAD TransID,

ProductCode,

ApplyMap('MapProducts', ProductCode, 'UNKNOWN') As ProductName,

Quantity,

...

FROM SomeSalesSource;

//Now we don't need the Products table any more

//(The mapping table is automatically dropped)

DROP Table Products;

This has the advantage of reducing the number of tables and associations in your model which may speed up front end performance, reduce the model size and ApplyMap is much, much faster than performing joins, especially on large data sets.

Hope that helps

Jonathan

What is a mapping table?

Anonymous — Mon, 05 Mar 2012 21:17:27 GMT

Thank you guys...for clearing my doubts