multiply 2 fields from different tables

[ELI4]

Hi there. How can I multiply 2 fields from different tables? Any Suggestions?

Thanks!

[stevejoyce]

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;

[ELI4]

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 ?

[stevejoyce]

can you share your script code?  are you joining before you do multiplication, and not in the same statement?

[ELI4]

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);

[stevejoyce]

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.

[ELI4]

When i want to do 

 (Sales-CostPerUnit) as Benefit;

error appears:

Field 'CostPerUnit' not found

[stevejoyce]

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);

[ELI4]

8 hours with this. You're a genius!!!
THANK YOU THANK YOU THANK YOU

[stevejoyce · Accepted solution]

No problem, glad you have it resolved!  Please mark as solution so thread can be marked appropriately.