Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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
this is what i was looking for! thank you!