4 Replies Latest reply: Sep 26, 2014 1:26 AM by Rachel Boey RSS

    Calculation between 2 tables

      Hi, I need to sum up the fieldnames in red: Opening Balance and sum, but they are in different tables. How can I do it? Currently the table clbal contains all variables from bal and clbal tables. The words in blue are the common variables.

       

      load*,date(timestamp#(Date, 'YYYYMMDDhhmm')) as DATE;

      bal:

      LOAD Date,

           storelocation AS StoreLocation,

           Opening_Balance as openingbal

      FROM

      [D:\<filename>.txt]

      (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

       

      clbal:

      load *,if(peek(StoreLocation)=StoreLocation,peek(openingbal),0) as Closing_Balance,

          openingbal as Opening_Balance

          resident bal order by StoreLocation,Date desc;

       

      load *,sqlDate as DATE,

      sqlStoreLocation as StoreLocation,

      Receipts+Returns+transfer_out+transfer_in+

      issue_from_store+intmxmp+return_to_store+[total_receipts-returns]+stock_adjustments+

      adjustments_to_current_balance+loan_transfer_out+loan_transfer_in as sum;

       

      Thanks.

        • Re: Calculation between 2 tables
          jagan mohan rao appala

          Hi,

           

          Check this script

           

          bal:

          LOAD Date,

               storelocation AS StoreLocation,

               Opening_Balance as openingbal

          FROM

          [D:\<filename>.txt]

          (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

           

          clbal:

          load *,if(peek(StoreLocation)=StoreLocation,peek(openingbal),0) as Closing_Balance,

              openingbal as Opening_Balance

              resident bal order by StoreLocation,Date desc;

           

          DROP TABLE bal;

           

          LEFT JOIN (clbal)

          load *,

          sqlDate as DATE,

          sqlStoreLocation as StoreLocation,

          Receipts+Returns+transfer_out+transfer_in+

          issue_from_store+intmxmp+return_to_store+[total_receipts-returns]+stock_adjustments+

          adjustments_to_current_balance+loan_transfer_out+loan_transfer_in as sum

          FROM Table2;

           

          Regards,

          Jagan.