Multiple lookup/applymap

[viveksingh]

Hi experts,

I have to do multiple lookup/ apply map tables. Based on a condition

table_1:

AGENT  BRANCH  CODE

A1            Hyd           101

A2            Hyd           102

A3            Chn           103

.                 .                

.                 .

.                 .

.                 .

A10          Mum          107

Reference_Table:

REF_AGENT   REF_BRANCH   REF_CODE

A1                          Hyd                  111

A3                          Pnq                  119

A5                           KL                   221

here if AGENT_NAME in table_1 is same with REF_AGENT in reference table

then it should check for branch also.if both are same then the table 1 should update the cide like in reference Table

beliw is exoected one

AGENT  BRANCH  CODE

A1            Hyd           111

A2            Hyd           102

A3            Chn           103

.                 .                

.                 .

.                 .

.                 .

A10          Mum          107

[prieper]

aircode:

mapAgtBranch:
MAPPING LOAD AGENT&BRANCH, CODE FROM Reference ....;

Table1:

LOAD AGENT, BRANCH, OriginalCODE, IF(CODE = '#', OriginalCode, CODE) AS CODE;

LOAD AGENT, BRANCH, CODE as OriginalCODE, APPLYMAP('mapAgtBranch', AGENT&BRANCH. '#') AS CODE FROM ....;

HTH

Peter

[m_s]

Hello vivek,

try the following script:

table_ref:

LOAD * Inline

[REF_AGENT,REF_BRANCH,REF_CODE

A1,Hyd,111

A3,Pnq,119

A5,KL,221];

m_table_ref:

Mapping load

REF_AGENT & '|' & REF_BRANCH,

REF_CODE

Resident table_ref;

table_1:

LOAD

AGENT,

BRANCH,

ApplyMap('m_table_ref', AGENT & '|' & BRANCH, CODE) as CODE

Inline

[AGENT,BRANCH,CODE

A1,Hyd,101

A2,Hyd,102

A3,Chn,103];

Cheers,

Mathias

[viveksingh]

‌hi thanks for the repl.

is the applymap written correct ?

[m_s]

Yes, you should be able to copy & paste this script and it will work.

Then you need to replace the INLINE data source with you actual data.

[viveksingh]

I tried, but CODE is having only null.

[viveksingh]

‌any help on this

[m_s]

See attached .qvw file using my script. If it does not work with your data you have to provide more information.

Cheers,

Mathias

[viveksingh]

‌Thanks for the reply Matias. I cannot open qvw in my mobile. Could you please show the script.

Thanks,

[m_s]

I just copied the script from my previous post but sure, here you go again:

table_ref:

LOAD * Inline

[REF_AGENT,REF_BRANCH,REF_CODE

A1,Hyd,111

A3,Pnq,119

A5,KL,221];

m_table_ref:

Mapping load

REF_AGENT & '|' & REF_BRANCH,

REF_CODE

Resident table_ref;

table_1:

LOAD

AGENT,

BRANCH,

ApplyMap('m_table_ref', AGENT & '|' & BRANCH, CODE) as CODE

Inline

[AGENT,BRANCH,CODE

A1,Hyd,101

A2,Hyd,102

A3,Chn,103

A10,Mum,107];