Apply Map

[upaliwije]

Hi

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

[maxgro · Accepted solution]

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

[upaliwije]

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

[maxgro]

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

];

[upaliwije]

Thanks a lot

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

Great help

[maxgro]

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