3 Replies Latest reply: May 14, 2012 11:44 AM by ALEXEY VOYTSEKHOVSKIY RSS

    MAPPING LOAD from a loaded table

      Let’s say, I have already loaded a table with some sales headers. Then, I would like to get one column to map it onto another table,which is not directly linked to the sales headers. My solution is just to load it again with MAPPING LOAD statement.

       

      However, I suppose there should be a simplier and more effective approach to this task. Do you have any ideas how I can make it working? How not to load the same table again?

       

      Thank you!

       

      The sample:

       

      Lines://fetching all invoice headers from CustInvoiceJour table

      LOAD
              
      SALESID & '_' &
              
      INVOICEID & '_' &
              
      INVOICEDATE & '_' &
              
      NUMBERSEQUENCEGROUP                   as Sales_Key,           INVOICEACCOUNT                          as Sales_ClientId,                     
           
      SALESORIGINID                            as Sales_Origin,
           
      PAYMENT                                  as Sales_PaymentTerms;

       

      SQL SELECT *
      FROM
      $(DBName).dbo.CUSTINVOICEJOUR

       

      ….

       

      SalesClientsMap:
      //fetching all clients from CustInvoiceJour table

      MAPPING LOAD Distinct
              
              
      LEDGERVOUCHER & '_' &
              
      INVOICEDATE                           as Ledger_key,              INVOICEACCOUNT                          as Sales_ClientId;                     

       

      SQL SELECT *
      FROM
      $(DBName).dbo.CUSTINVOICEJOUR

       

      Ledger://fetching transactions from General Ledger for the three accounts by adding themto Lines

      Concatenate (Lines)

      LOAD
              'Ledger'                                                  
      as Type,       

            POSTING,
           
      ACCOUNTNUM                                             as Ledger_Account,  
           
      ApplyMap('SalesClientsMap', VOUCHER & '_' &TRANSDATE, null())                                                    as Sales_ClientId,          ,
            -
      AMOUNTMST                                             as Sum_Posted
           
      ;

      SQL SELECT *
      FROM
      $(DBName).dbo.LEDGERTRANS

       

      Thank you!