Add column on load

[#1]

Hi,

Making it simpler, say I have two input tables:

Table 1 - Millions of Rows

trade_id - broker_id - client_id - exchange_id

Table 2 - 10 Rows

issue_id - score - broker_id - client_id - exchange_id

I need to add another column issue_id to table 1. issue_id should be filled with the issue_id from 2) if a specific broker/client/exchange combination is in there. If it's not, fill in 0.

Pseudo-Code:

Add issue_id to 1

For Each row in 1):

     For Each row in 2):

          If 1.broker_id == 2.broker_id && 1.client_id == 2.client_id && 1.exchange_id == 2.exchange_id:

               1.issue_id = 2.issue_id

          Else

               1.issue_id = 0

     Next Row

Next Row

How would I implement this in QlikView please? Do I run this on load or as a macro afterwards?

Thank you!

Oli

[Gysbert_Wassenaar · Accepted solution]

A mapping table and applymap should work

MapIssueID:
mapping load autonumber(broker_id &'|'& client_id &'|'& exchange_id) as Key, issue_id
from table2;

Table1:
load *,  applymap('MapIssueID', Key, 0) as issue_id;
load
    trade_id
    , broker_id
    , client_id
    , exchange_id
    , autonumber(broker_id &'|'& client_id &'|'& exchange_id) as Key
from table1;

drop field Key;