Applymap instead of left join

[#1]

I have two tables;

Table 1:  Client_ID, Specialty, State, ...    (1.5 million rows)

Table 2: Client_ID, Corp_Specialty, Corp_State,......   (2 million rows)

I want to replace Specialty and State from Table 1 with Corp_Specialty and Corp_State of table 2 for every matching Client_ID. I can do this using left join, but I want to use mapping load and Applymap. Can anyone help me with the syntax? Thanks!

[johnw]

Maybe this?

SpecialtyMap:
MAPPING LOAD
Client_ID
,Corp_Specialty
RESIDENT Table2
;
StateMap:
MAPPING LOAD
Client_ID
,Corp_State
RESIDENT Table2
;

Table1:
LOAD
Client_ID
,applymap('SpecialtyMap',Client_ID,Specialty) as Specialty
,applymap('StateMap',Client_ID,State) as State
...
;

[#3]

Does it behave any different if I've multiple instance of same Client_ID in Table1? For some reason in my case Specialty and State both gets values as Client_ID. So, if I do distinct of Specialty and State after the mapping I get Client_IDs and not the real values.

[johnw]

I would anticipate trouble if Client_ID is not unique in table TWO.  In that case, QlikView wouldn't know which Corp_Speciality to map.  It might do something like apply the first one, or just return null, or who knows what.  But it shouldn't make any difference if Client_ID is not unique in table one.

[danielrozental]

I believe Applymap would apply the first one found in the mapping table.