Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
...
;
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.
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.
I believe Applymap would apply the first one found in the mapping table.