topic Applymap instead of left join in QlikView

Applymap instead of left join

Mon, 08 Aug 2011 23:58:06 GMT

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!

Re: Applymap instead of left join

johnw — Tue, 09 Aug 2011 00:05:15 GMT

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
...
;

Applymap instead of left join

Wed, 17 Aug 2011 21:09:49 GMT

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.

Applymap instead of left join

johnw — Tue, 23 Aug 2011 16:56:19 GMT

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.

Applymap instead of left join

danielrozental — Tue, 23 Aug 2011 17:04:44 GMT

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