Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there. How can I multiply 2 fields from different tables? Any Suggestions?
Thanks!
No problem, glad you have it resolved! Please mark as solution so thread can be marked appropriately.
Either join the fields from different tables into same table and then do multiplication, or create a mapping table for one of the 2 tables and use applymap.
Example:
table1:
load * inline [
dim1, value1
a, 10
b, 20
c, 30
];
table2:
mapping load * inline [
dim2, value2
a, 50
b, 60
c, 70
]
;
table3:
load
dim1 as dim3
,value1 * applymap('table2', dim1) as value3
resident table1;
Hi steve. thanks for answering. I used join to join the tables but when I want to do the multiplication, it does not recognize the field that I joined. why ?
can you share your script code? are you joining before you do multiplication, and not in the same statement?
Salesnews:
Load
*,
(Sales-CostPerUnit) as Benefit;
LOAD
ItemID,
Date,
CampaignID,
DocumentNumber,
StoreHanut,
"Time",
SalesmanID,
if( Quantity>= 0, 'Sold', 'Return' ) as Status,
ApplyMap('CampaingID_Map', CampaignID , 'No Campaign' ) as NameofCampaign,
TotalPriceBeforVat,
Quantity,
(TotalPriceBeforVat*Quantity*1.17) as Sales
FROM [lib://ExtractDB/Salesnews.qvd] (qvd);
left Join
Stock:
LOAD
ItemID,
Cost as CostPerUnit
FROM [lib://ExtractDB/Stock.qvd]
(qvd);
They only multiplication i see you doing is all from Salesnews.qvd so i'm not sure where your issue is. Add Exit script; or step through with the debugger after your join and before your multiplication. Make sure you have the right field name.
When i want to do
(Sales-CostPerUnit) as Benefit;
error appears:
Field 'CostPerUnit' not found
Yes, i believe you had another post open with same issue. You are joining CostPerUnit AFTER you do this calculation. So at the time you do Sales-CostPerUnit, this field doesn't exist in that table yet. I assume there is 1 CostPerUnit for each ItemID, so we can do mapping load for this...
map_ItemID_CostPerUnit:
mapping LOAD
ItemID,
Cost as CostPerUnit
FROM [lib://ExtractDB/Stock.qvd]
(qvd);
Salesnews:
Load
*,
(Sales-applymap('map_ItemID_CostPerUnit', ItemID)) as Benefit;
LOAD
ItemID,
Date,
CampaignID,
DocumentNumber,
StoreHanut,
"Time",
SalesmanID,
if( Quantity>= 0, 'Sold', 'Return' ) as Status,
ApplyMap('CampaingID_Map', CampaignID , 'No Campaign' ) as NameofCampaign,
TotalPriceBeforVat,
Quantity,
(TotalPriceBeforVat*Quantity*1.17) as Sales
FROM [lib://ExtractDB/Salesnews.qvd] (qvd);
8 hours with this. You're a genius!!!
THANK YOU THANK YOU THANK YOU
No problem, glad you have it resolved! Please mark as solution so thread can be marked appropriately.