Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.