APPLY MAP

[upaliwije]

Hi, Friends,

I have two tables

POLICY_NO	POL_TYPE
TA00171A0000089	R
00EM1D000574	R
00GM1A000393	R
00KA1D000603	R
00KA1H000338	R
00NU1C000924	R
01CO1A006290	R
01CO1C005803	R
01CO1C005962	R
01CO1C005980	R

my script is for the 1st table is as follows

mapnew:

MAPPING LOAD TRIM(POLICY_NO) AS POLICY_NO, POL_TYPE

FROM

D:\Qlikview\QVD\FACT_Table\Renewable_to_new.qvd

(qvd);

my 2nd table is

POLICY_NO        POL_TYPE_N

EN00161C0000511  R

DG00161A0000971  R

WM00171D0000484  F

KD00141A0001079  R

TA00171A0000089  N

CO14151A0009541  R

COMC111A053251  R

CO12141C0002493  R

KD00061B001168  F

KD00061B001168  R

and the script for the apply map is shown below

  load *,

  IF(Match(POL_TYPE_N,'N'),ApplyMap('mapnew',POLICY_NO),POL_TYPE_N)AS POL_TYPE

  Resident growth;

But  my output is incorrect and shown below

POLICY_NO	POL_TYPE_N	POL_TYPE
EN00161C0000511	R	R
DG00161A0000971	R	R
WM00171D0000484	F	F
KD00141A0001079	R	R
TA00171A0000089	N	TA00171A0000089
CO14151A0009541	R	R
COMC111A053251	R	R
CO12141C0002493	R	R
KD00061B001168	F	F

I can not understand the reason for the above, I am expecting 'R' instead of policy No  under the POL_TYPE of the 5th Record of above table  Pls advise me

[tresesco]

Because there is no member 'TA00171A0000089' in your mapping table. Applymap by defaults returns the source value itself if there is no match in the mapping table.

[Anil_Babu_Samineni]

To understand clear where values are identified or not

IF(Match(POL_TYPE_N,'N'),ApplyMap('mapnew',POLICY_NO),'NULL') AS POL_TYPE

[maxgro]

with the data you posted it seems to work, see image and script below

Try to check if you have a space in the policy no TA00171A0000089

Renewable_to_new:

load * inline [

POLICY_NO POL_TYPE

TA00171A0000089 R

00EM1D000574 R

00GM1A000393 R

00KA1D000603 R

00KA1H000338 R

00NU1C000924 R

01CO1A006290 R

01CO1C005803 R

01CO1C005962 R

01CO1C005980 R

] (delimiter is spaces)

;

mapnew:

MAPPING LOAD TRIM(POLICY_NO) AS POLICY_NO, POL_TYPE

Resident  Renewable_to_new;

DROP Table Renewable_to_new;

growth:

load * inline [

POLICY_NO        POL_TYPE_N

EN00161C0000511  R

DG00161A0000971  R

WM00171D0000484  F

KD00141A0001079  R

TA00171A0000089  N

CO14151A0009541  R

COMC111A053251  R

CO12141C0002493  R

KD00061B001168  F

KD00061B001168  R

] (delimiter is spaces)

;

load

*,

IF(Match(POL_TYPE_N,'N'),ApplyMap('mapnew',POLICY_NO),POL_TYPE_N)AS POL_TYPE

Resident growth;

DROP Table growth;

[upaliwije]

Hi Anil

Then I get this output