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

    Join load and calculate fields with both tables

      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.

       

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


          HTH
          Peter

            • 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]
              ;
              [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.