Join load and calculate fields with both tables

[pdumas]

Hi,

After a join load, I need to calculate a field with the data from both tables

Example :

Table1 : Reference, Price

Table2 : Reference, Quantity

I want to build a new table with

Reference

Price from Table1

Quantity from Talbe2

Price*Quantity

Thanks

Pierre.

[prieper]

You will need to join both tables in order to calculate with the fields:

Table3: JOIN (Table1) LOAD * RESIDENT Table2;
LOAD *, Price*Quantity AS Cost RESIDENT Table3;
DROP TABLE Table3; DROP TABLE Table2; DROP TABLE Table1;

HTH
Peter

[johnw]

Joins are the way to go here. But technically, you don't need to do a join to use fields from two or more tables during a load. You can set up mapping tables:

[Price Map]:
MAPPING LOAD *
RESIDENT [Table1]
;
[Table3]:
LOAD
Reference
,Quantity
,applymap('Price Map',Reference) as "Unit Price"
,Quantity * applymap('Price Map',Reference) as "Total Price"
RESIDENT [Table 2]
;

One possible advantage is if you want a default value. When doing a join, mismatching rows will have null values. Using a map allows you to specify a default like 'Missing' if you wish to make it easier to search for these. You can always do that as a separate join step by joining the table back to itself, though.