topic Re: Multiple lookup/applymap in QlikView

Multiple lookup/applymap

viveksingh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Multiple lookup/applymap

prieper — Wed, 19 Sep 2018 08:15:32 GMT

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

Re: Multiple lookup/applymap

m_s — Wed, 19 Sep 2018 08:20:47 GMT

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

Re: Multiple lookup/applymap

viveksingh — Wed, 19 Sep 2018 09:06:03 GMT

‌hi thanks for the repl.

is the applymap written correct ?

Re: Multiple lookup/applymap

m_s — Wed, 19 Sep 2018 09:09:56 GMT

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.

Re: Multiple lookup/applymap

viveksingh — Wed, 19 Sep 2018 09:49:59 GMT

I tried, but CODE is having only null.

Re: Multiple lookup/applymap

viveksingh — Thu, 20 Sep 2018 06:43:45 GMT

‌any help on this

Re: Multiple lookup/applymap

m_s — Thu, 20 Sep 2018 07:08:23 GMT

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

Cheers,

Mathias

Re: Multiple lookup/applymap

viveksingh — Thu, 20 Sep 2018 07:39:24 GMT

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

Thanks,

Re: Multiple lookup/applymap

m_s — Thu, 20 Sep 2018 08:58:59 GMT

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