Apply Map Usage

[#1]

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:

[arasantorule]

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.

[muthukumar77]

Hi,

[qlikview979]

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;