What is a mapping table?

[Anonymous]

Hi,

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

Thanks

B

[magavi_framsteg · Accepted solution]

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.

[#3]

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

[jonathandienst]

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

[Anonymous]

Thank you guys...for clearing my doubts

~B