HOW TO IMPLEMENT ‘ QLIK VIEW APPLY MAP FUNCTIONALITY’ IN HANA GRAPHICAL VIEWS

ApplyMap is a lookup or mapping function in QlikView. This function takes one value as input and checks if that value exists in a mapping table and returns the corresponding values from the second column of the mapping table, if the value doesn’t exists it out puts the ‘Default value’.

Syntax of Applymap in QlikView:

Applymap (‘Mapping Table Name’,Mapping_Column,’Default value’).

In QlikView it is mandatory that the mapping table should have only two columns.

In HANA Graphical views we can implement this functionality by using ‘Outer Join’.

For this example we will take two tables Sales table (TBL_SALES) & ‘Customer table’ (MAP_TBL_CUSTOMER this will be our mapping table). In ‘MAP_TBL_CUSTOMER’ table all the information about the customer is stored, for this example we will take CUSTOMER_ID and CUSTOMER_NAME. In ‘TBL_SALES’ table we are having all the sales related details except in CUSTOMER_NAME column we don’t have data. Now we will see how to fill the CUSTOMER_NAME column in sales table based on CUSTOMER_ID using customer table as mapping table.

Sales data:

TBL_SALES

Note here customer name column wont have any data.

Customer data:

MAP_TBL_CUSTOMER

Step 1:

              Create a Graphical  view  (for this example Attribute view)and add both the sales and customer tables (TBL_SALES &                                      MAP_TABL_CUSTOMER) to the Data Foundation.

Step2:

             Now create a join between  ‘TBL_SALES’ & MAP_TABL_CUSTOMER’ using CUSTOMER_ID attribute ,with Join type as ‘Left Outer ‘ .

             Then select the CUSTOMER_NAME field from the Right table ‘MAP_TBL_CUSTOMER’.

Step3:

             Validate & Activate the view , do a data preview and you will find CUSTOMER_NAME column filed with data.

Step4 (Optional):                              

                                 If you want to replace the  null values with some ‘default values’  then simply create a Calculated column using the if                                                condition as shown below.

That’s it enjoy!.

Best Regards

Rafiq