topic Apply Map Usage in QlikView

Apply Map Usage

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am not clear on usage of Applymap()

In below scenario "Order_Id" is common column between "Customers" and "Orders" tables

ApplyMap() is used to fetch "Item" column from "Orders" table and add to "Customers" table, below mentioned also the script and table viewer screenshot

If I do not use ApplyMap() , simply load both tables (Customers and Orders) and create two Listboxes one for "Customer_Name" and another for "Item" then I can directly show "Item" associated with "Customer_Name" by selecting any value from "Customer_Name" listbox

Customers:

Customer_Id	Customer_Name	Order_Id
CUS001	Dennis	ORCUS00101
CUS001	Dennis	ORCUS00102
CUS002	Mariya	ORCUS00201
CUS002	Mariya	ORCUS00202
CUS003	Bob	ORCUS00301
CUS003	Bob	ORCUS00302
CUS003	Bob	ORCUS00303

Orders:

Order_Id	Item	Quantity
ORCUS00101	Toothpaste	2
ORCUS00102	Grocery	1
ORCUS00201	Bread	1
ORCUS00202	Chocolate	5
ORCUS00301	Vegetable oil	1
ORCUS00302	Ginger	8
ORCUS00303	Ketchup	2

Script:

Orders:
LOAD Order_Id,
Item,
Quantity
FROM

(ooxml, embedded labels, table is Orders);

Map:
Mapping LOAD
Order_Id,
Item
Resident Orders;

Customers:
LOAD Customer_Id,
Customer_Name,
Order_Id,
ApplyMap('Map',Order_Id,'NA') As Itemname
FROM

(ooxml, embedded labels, table is Customers);

Table Viewer:

Re: Apply Map Usage

arasantorule — Tue, 05 Jul 2016 03:44:59 GMT

Hi,

Apply map is used to map a pair of unique values related to each other.

Applymap() in above example is applicable only if each order_id will always have only one Item.

Please refer the below for more details.

Don't join - use Applymap instead

Thanks.

Re: Apply Map Usage

muthukumar77 — Tue, 05 Jul 2016 05:07:11 GMT

Hi,

Re: Apply Map Usage

qlikview979 — Tue, 05 Jul 2016 05:50:27 GMT

Hi,

You Have One common field(Order_Id ) between two table that's why you are getting Association between

"Customer_Name" and "Item". if you drop the common field in any one table your not able to get the association.

Please check my attached file for Applaymap

T1:

load * Inline [

Order_Id, Item, Quantity

ORCUS00101, Toothpaste, 2

ORCUS00102, Grocery ,1

ORCUS00201, Bread ,1

ORCUS00202, Chocolate, 5

ORCUS00301, Vegetable oil, 1

ORCUS00302, Ginger, 8

ORCUS00303, Ketchup, 2

];

T2:

mapping load

Order_Id ,

Item

Resident T1;

T3:

mapping load

Order_Id ,

Quantity

Resident T1;

DROP Table T1;

T4:

load * Inline [

Customer_Id, Customer_Name, Order_Id

CUS001, Dennis ,ORCUS00101

CUS001, Dennis ,ORCUS00102

CUS002, Mariya ,ORCUS00201

CUS002, Mariya ,ORCUS00202

CUS003, Bob ,ORCUS00301

CUS003, Bob ,ORCUS00302

CUS003, Bob ,ORCUS00303

];

NoConcatenate

T5:

load

Customer_Id,

Customer_Name,

Order_Id,

ApplyMap('T2',Order_Id,'') as Applymap1,

ApplyMap('T3',Order_Id,'') as Applymap2

Resident T4;

DROP table T4;