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

Applymap instead of left join

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!

4 Replies
MVP
MVP

Re: Applymap instead of left join

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

Not applicable

Applymap instead of left join

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.

MVP
MVP

Applymap instead of left join

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.

Highlighted
danielrozental
Honored Contributor II

Applymap instead of left join

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

Community Browser