Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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;
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
Hi all,
I thank you for your reply I will use Left Join
its work
Many thanks for all
Best Regards,
Said
don't forget to mark the correct and helpful answers