topic Re: Apply Map in QlikView

Apply Map

upaliwije — Wed, 02 Sep 2015 21:39:24 GMT

I have following table loaded into my QV doc

CLM:

LOAD

POLICY_NO,

POLICY_PERIOD,

BCOD,

PRD_CODE,

PREMIUM

FROM

E:\premiumC3.qvd

(qvd);

I want to use apply may from following table where I have to match two fields marked in red.

LOAD

POL_POLICY_NO as POLICY_NO,

POLICY_PERIOD ,

PRL_DESCRIPTION,

PPR_PREMIUM,

PPR_PERCENTAGE

FROM

E:\PRE_BREAKUP.qvd

using apply may I want to join two tables where 3 fields marked in blue to be combined.

Pls help me to write script

Re: Apply Map

maxgro — Wed, 02 Sep 2015 21:52:46 GMT

in your case I would use a left join but (3 fields), if you want to map

Map1: Mapping LOAD

POL_POLICY_NO & '-' & POLICY_PERIOD ,

PRL_DESCRIPTION

FROM E:\PRE_BREAKUP.qvd;

Map2: Mapping LOAD

POL_POLICY_NO & '-' & POLICY_PERIOD ,

PPR_PREMIUM

FROM E:\PRE_BREAKUP.qvd;

Map3: Mapping LOAD

POL_POLICY_NO & '-' & POLICY_PERIOD ,

PPR_PERCENTAGE

FROM E:\PRE_BREAKUP.qvd;

CLM:

LOAD

POLICY_NO,

POLICY_PERIOD,

BCOD,

PRD_CODE,

PREMIUM,

ApplyMap('Map1', POLICY_NO & '-' & POLICY_PERIOD) as PRL_DESCRIPTION,

ApplyMap('Map2', POLICY_NO & '-' & POLICY_PERIOD) as PPR_PREMIUM,

ApplyMap('Map3', POLICY_NO & '-' & POLICY_PERIOD) as PPR_PERCENTAGE

FROM

E:\premiumC3.qvd

(qvd);

you can reduce to 1 the mapping table if you concatenate the second fields of every map table and, in applymap, use subfield to extract the different description

Re: Apply Map

upaliwije — Wed, 02 Sep 2015 22:31:45 GMT

Thanks for you help. It is working. I shall be much great full to you if you could please send me the script suggested by you to reduce to 1 mapping table by concatenating fields

Re: Apply Map

maxgro — Wed, 02 Sep 2015 22:46:49 GMT

small example with your fields and some data (inline, replace with your qvd)

Map1:

Mapping LOAD

POL_POLICY_NO & '-' & POLICY_PERIOD ,

// 1 map, 3 fields - separated

PRL_DESCRIPTION & '-' & PPR_PREMIUM & '-' & PPR_PERCENTAGE

inline

[

POL_POLICY_NO ,POLICY_PERIOD , PRL_DESCRIPTION, PPR_PREMIUM, PPR_PERCENTAGE

1,1,a,b,c

1,2,aa,bb,cc

1,3,aaa,bbb,ccc

2,2,x,y,z

];

CLM:

LOAD

POLICY_NO,

POLICY_PERIOD,

BCOD,

PRD_CODE,

PREMIUM,

// subfield 1 2 3 from Map1 to peek the different field

subfield(ApplyMap('Map1', POLICY_NO & '-' & POLICY_PERIOD), '-', 1) as PRL_DESCRIPTION,

subfield(ApplyMap('Map1', POLICY_NO & '-' & POLICY_PERIOD), '-', 2) as PPR_PREMIUM,

subfield(ApplyMap('Map1', POLICY_NO & '-' & POLICY_PERIOD), '-', 3) as PPR_PERCENTAGE

inline

[

POLICY_NO ,POLICY_PERIOD , BCOD, PRD_CODE, PREMIUM

1,1,10,10,10

1,2,100,100,100

1,3,1000,1000,1000

2,2,20,200,2000

];

Re: Apply Map

upaliwije — Wed, 02 Sep 2015 22:53:33 GMT

Thanks a lot

Sorry to disturb you Pls if you can write the script for Left Join also.

Great help

Re: Apply Map

maxgro — Thu, 03 Sep 2015 11:35:03 GMT

CLM:

LOAD

POLICY_NO,

POLICY_PERIOD,

BCOD,

PRD_CODE,

PREMIUM

FROM

E:\premiumC3.qvd

(qvd);

left join (CLM)

LOAD

POL_POLICY_NO as POLICY_NO,

POLICY_PERIOD ,

PRL_DESCRIPTION,

PPR_PREMIUM,

PPR_PERCENTAGE

FROM

E:\PRE_BREAKUP.qvd