topic Re: APPLY MAP in QlikView

APPLY MAP

upaliwije — Sat, 28 Oct 2017 12:08:25 GMT

Hi, Friends,

I have two tables

POLICY_NO

POL_TYPE

TA00171A0000089

00EM1D000574

00GM1A000393

00KA1D000603

00KA1H000338

00NU1C000924

01CO1A006290

01CO1C005803

01CO1C005962

01CO1C005980

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

Re: APPLY MAP

tresB — Sat, 28 Oct 2017 12:25:46 GMT

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.

Re: APPLY MAP

Anil_Babu_Samineni — Sat, 28 Oct 2017 12:27:14 GMT

To understand clear where values are identified or not

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

Re: APPLY MAP

maxgro — Sat, 28 Oct 2017 12:40:52 GMT

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;

Re: APPLY MAP

upaliwije — Sat, 28 Oct 2017 13:44:25 GMT

Hi Anil

Then I get this output