Mapping

[emteam162]

Hi All,

Is that-is a way to do that let me explain :

i have this table

Purchase:

LOAD * Inline [

PurchaseID,Product,PurchasePrice,Quantity,PurchaseDate

P9, Product1, 8,  20, 15/10/2010

P11, Product1, 7,  10, 09/10/2010

P12, Product1, 9,  20, 01/10/2010

];

and i want apply function Mapping

Temp:

Mapping LOAD

Product,PurchasePrice

Resident Purchase;

DROP Table Purchase;

Sales:

LOAD ApplyMap('Temp', Product ) as PurchasePrice,* Inline [

SalesID,Product,SalesPrice,Quantity,SalesDate

S1, Product1, 10,  10, 10/10/2010

];

the final result I want is :

Product	PurchasePrice	Quantity	SalesDate	SalesID	SalesPrice
Product1	8	10	10/10/2010	S1	10
Product1	7	10	10/10/2010	S1	10
Product1	9	10	10/10/2010	S1	10

or

Product	PurchasePrice	PurchasePrice	PurchasePrice	Quantity	SalesDate	SalesID	SalesPrice
Product1	8	7	9	10	10/10/2010	S1	10

I attach an example.

Many thanks for your help

Said

[sunny_talwar]

I don't think mapping would work here because you have one product field mapped to three different purchase price. Mapping table can only be used when you have unique combinations. In your case, it would make sense to do a join.

[MK_QSL · Accepted solution]

Purchase:
LOAD * Inline 
[
  PurchaseID, Product, PurchasePrice, Quantity, PurchaseDate
  P9, Product1, 8,   20, 15/10/2010
  P11, Product1, 7,   10, 09/10/2010
  P12, Product1, 9,   20, 01/10/2010
];


Sales:
LOAD 
  * 
Inline 
[
  SalesID, Product, SalesPrice, Quantity, SalesDate
  S1, Product1, 10,   10, 10/10/2010
];


Left Join (Sales) Load Product, PurchasePrice Resident Purchase;


Drop Table Purchase;

[avinashelite]

mapping works on the unique key if you have duplicated keys only the first value will be considered  in your case try with the Join it should work

try like this

Purchase:

LOAD * Inline [

PurchaseID,Product,PurchasePrice,Quantity,PurchaseDate

P9, Product1, 8,  20, 15/10/2010

P11, Product1, 7,  10, 09/10/2010

P12, Product1, 9,  20, 01/10/2010

];

and i want apply function Mapping

Sales:

LOAD 

Product  as PurchasePrice

,* Inline [

SalesID,Product,SalesPrice,Quantity,SalesDate

S1, Product1, 10,  10, 10/10/2010

];

Join(Sales)

LOAD

Product,

PurchasePrice

Resident Purchase;

DROP Table Purchase;

to avoid syndicate key rename the fields 

[emteam162]

Hi all,

I thank you for your reply I will use Left Join

its work

Many thanks for all

Best Regards,

Said

[avinashelite]

don't forget to mark the correct and helpful answers