Qlikview Mapping

Qlik is a software company that provides Business Intelligence software named QlikView and a data visualization software called Qlik Sense. It is basically from Sweden but now it is headquartered in the United States. QlikView is a self-service Business Intelligence where organization users can view data on the fly. The data exploration given by QlikView is available to multiple users.

Learn QlikView Mapping

Data Mapping in DataBase:

It is a process of creating data elements from two distinct data models for the purpose of data transformation kind of data integration tasks.

Want to Become an Expert in QlikView? Then visit here to Learn QlikView Training Online

QlikView Mapping:

If you want to map countries and top performers in outside sales in every location of an organization can be done within no time.

An enterprise has multiple locations say the USA, India, UK, and Australia. Individually, within the country, there are multiple centers. Let’s say in India, there are 3 centers - Hyderabad, Delhi, and Pune. Each center has 300 outside sales representatives working on different products. Individually, every center maintains the performance of each outside sales representative in a data sheet.  Now, selecting the top performer over country-wise and continent-wide requires the top management to collect data from different units and then analyze them to find the best one.  Using QlikView, all the data is integrated and analyzed based on the questions the user raised and outputs the results.

One can get the Top performers of an enterprise from each organization or center as

“Salesman Name  ||  Country”

This output is attained with a simple Syntax:  Mapping( loadstatement | selectstatement ).

There are many functions available in this Mapping technique to handle database table mapping.

Mapping tables or Mapping load serves as an alternative to the Join statement in the database. Mapping to the load statement tells Qlikview that a table is Mapped with another. A mapping table can have 2 columns-  the lookup value and the mapping value. Mapping Tables are temporary tables as they are automatically removed from the data model by the end of the execution of the script.

 

Related Article: Qlikview Questions

Apply map() function:

The function is used for mapping an expression to that stored mapping table.

The syntax is: applymap('mapname', expr [ , defaultexpr ] )

mapname is the name of a mapping table.
expr is the expression.
defaultexpr is an expression that is used as a default mapping value if the mapping table does not contain any value for expr. If no default value is given, the value of expr will be returned as it is.

Syntax and Use case of Mapping Tables

For an invoice directory, when we need to calculate the total invoice amount, the due amount and the final payment, etc. in a retail store. We will maintain the data table that contains customer-selected items, an InvoiceID, amount received.

Related Article: Incremental Load in Qlikview

The Mapping Table or Mapping Load function is created by the following syntax.

Map_ReceiptAmount:
Mapping Load
   Invoice ID as ReceiptInvoiceID,
   Amount as  Receipt Amount
From
{ data source}

From the above commands, we are creating a Mapping table with Invoice IF and Amount received. Place where the data table is stored in the system.

Now we use ApplyMap function on the above tale with the syntax below.

ApplyMap (‘Map_ReceiptAmount’,InvoiceID,0)

Visit here to learn QlikView Online Training in Bangalore

Load the required information like the Mapping table, the key field, and the default value. The full script can be:

(InvoiceDetails) :
LOAD InvoiceDetailID,
          Description,
          InvoiceID,
          ApplyMap (‘Map_ReceiptAmount’,InvoiceID,0) as ReceiptAmount
FROM    
{ Destination source file }

You can see the result with a new column ReceiptAmount added to the invoice table.

Explore QlikView Sample Resumes! Download & Edit, Get Noticed by Top Employers!

Features of QlikView Mapping

• Mapping Table is like Lookup Function which takes an input value, checks if this value exists in a mapping table, and returns it to the 2nd column of the mapping table.

• First, a Mapping table is created and then a Map is applied to it.

• A Map is held in the memory until the script runs. It is collapsed after that making it a temporary table.

• The temporary stay nature of the mapping table does not affect the main data.

• They can be reused as and when needed multiple times in a script.

• The mapping table has the first field as the Key and the second field as the mapped value  ALWAYS.

• When referring to a Map name, a single script is used.

• We can translate the map multiple times.