Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community members,
I have one more newbie-question that I have tried to solve on my own for many hours...
Two tables:
OrderNo | Product | Quantity |
1 | A | 2 |
2 | A | 3 |
3 | B | 2 |
4 | C | 4 |
Product | Price per Unit |
A | 3 |
B | 4 |
C | 5 |
D | 6 |
All I want to do is to add to my first table the value of the order, i.e. Quantity x Price per Unit. I am trying to do this in my load script in order to have this information ready for easy analysis in my app.
Again, I highly appreciate your help!
Thanks!
Tobi
Price_Mapping:
Mapping load
Product,
PricePerUnit
From/Resident Table_Name;
OrderTable:
LOAD *,
Quantity * PricePerUnit as Value;
LOAD
OrderNo,
Product,
Quantity,
Applymap('Price_Mapping', Product, 0) as PricePerUnit //if any of product have no value it will display 0.
From/Resident Table_Name;
hi, you can do it with a Join to have all fields in the same table or using a mapping table, like:
mapPrice:
Mapping LOAD Product, Price
From/Resident...; // load from table
OrderTable:
LOAD *, Quantity*Price as OrderValue;
LOAD OrderNo, Product, Quantity,
Applymap('mapPrice', Product) as Price
From/resident... // load from table
Hi,
You can also use join. Like
Base:
Load * from table1;
Left Join
Load Product, [Price Per Unit] from table2;
NoConcatenate
Final:
Load *, Quantity * [Price Per Unit] as Sale_price Resident Base;
Drop table Base;
Regards,
Prashant Sangle
One solution is.
tab1:
LOAD * INLINE [
OrderNo, Product, Quantity
1, A, 2
2, A, 3
3, B, 2
4, C, 4
];
Left Join(tab1)
Price:
LOAD * INLINE [
Product, Price per Unit
A, 3
B, 4
C, 5
D, 6
];
tab2:
LOAD OrderNo, Quantity*[Price per Unit] As Value
Resident tab1;
Price_Mapping:
Mapping load
Product,
PricePerUnit
From/Resident Table_Name;
OrderTable:
LOAD *,
Quantity * PricePerUnit as Value;
LOAD
OrderNo,
Product,
Quantity,
Applymap('Price_Mapping', Product, 0) as PricePerUnit //if any of product have no value it will display 0.
From/Resident Table_Name;
go with what @rubenmarin suggested to avoid any join. But you can also use below to minimize load statements
mapPrice:
Mapping LOAD Product, Price
FROM Product; // load from table
OrderTable:
LOAD OrderNo,
Product,
Quantity,
Quantity * Applymap('mapPrice', Product,0) as Sales
FROM Order // load from table
Dear Usama,
thank you very much. The script does exactly what it‘s supposed to do. It generates (at least in the context of my app) a synthetic key. For me, this does not seem to be any problem. I just wanted to share this information in case someone else has a similar problem and finds this thread in the form.
Thanks again to all you great people who were so kind to answer my question!
Toni