Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
];
Thanks a lot
Sorry to disturb you Pls if you can write the script for Left Join also.
Great help
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