Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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!

1 Solution

Accepted Solutions
jason_michaelid
Honored Contributor II

Re: MAPPING LOAD from a loaded table

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

3 Replies

MAPPING LOAD from a loaded table

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_michaelid
Honored Contributor II

Re: MAPPING LOAD from a loaded table

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

Not applicable

Re: MAPPING LOAD from a loaded table

this is what i was looking for! thank you!

Community Browser