MAPPING LOAD from a loaded table

[#1]

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!

[Miguel_Angel_Baeyens]

Hi Alex,

I don't see anything wrong with that, and I don't see any simpler way to work that around other than, perhaps, joining both tables. Depending on the values, it may take a huge amount of time and memory, and probably it's not worth it.

But if you are loading dozens of thousands of rows, you may give a try to first STORE the fields you want into a QVD file then load from this QVD. If the load is optimized, it should be quite faster than the RESIDENT load. As it's a mapping table, there's not need to rename fields.

Note that from version 10 on you can load mapping tables from optimized QVD files.

Hope that helps.

Miguel

P.S.: Well, yes, you can do that in the SQL statement, but I don't see any reason to leverage on the RDBM.

[Jason_Michaelides · Accepted solution]

Just use a RESIDENT load -

SalesClientsMap:
//fetching all clients from CustInvoiceJour table

MAPPING LOAD Distinct
        
         Ledger_key,

         Sales_ClientId;                     

RESIDENT Lines;

Hope this helps,

Jason

[#4]

this is what i was looking for! thank you!