2 Replies Latest reply: Feb 9, 2010 7:06 PM by John Witherspoon RSS

    Join load and calculate fields with both tables


      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


      Price from Table1

      Quantity from Talbe2





        • Join load and calculate fields with both tables
          Peter Rieper

          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;


            • Join load and calculate fields with both tables
              John Witherspoon

              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]
              ,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.