topic Re: Applymap to fill values in QlikView

Applymap to fill values

qlikwiz123 — Sat, 16 Nov 2024 00:27:47 GMT

Table A:

Name	ID
A	1
B
C	5
D
E	7

Table B:

Name	ID
A	20
B	2
C	99
D	9
E
F	8
I	10
J	11
K	15

Result:

Name	ID
A	20
B	2
C	99
D	9
E	7

I need to use Apply Map and populate 'ID' column using the two tables based on these conditions.

1. If 'ID' in Table A is blank, then fill it with 'ID' from Table B

2. If 'ID' in Table A has a value, replace it with 'ID' from Table B

3. If 'ID' in Table A has a value and 'ID' in Table B is null, then use the value from table A.

I have tried

if(len(ID) > 0, ID, ApplyMap('NameIdMap', Name)) as ID

Applymap('fieldmap',Name,ID) 			as ID1,
    if(ID>0,ID,Applymap('fieldmap',Name) ) 	as ID2;

But they are failing the 3rd condition I have here.

Please help

Re: Applymap to fill values

lironbaram — Thu, 18 Jun 2020 05:22:41 GMT

have a look at this

fieldmap: Mapping load * inline [ Name,ID A,20 B,2 C,99 D,9 E, F,8 I,10 J,11 K,15 ]; Table1: load Name, if(Applymap('fieldmap',Name,ID)>0, Applymap('fieldmap',Name,ID),ID) as ID; load * Inline [ Name,ID A,1 B, C,5 D, E,7 L,10 ];

Re: Applymap to fill values

qlikwiz123 — Thu, 18 Jun 2020 14:12:38 GMT

@lironbaram Hi, this might work. Let me try. Thank you. But what happens when the ID is alphanumeric?

I tried

if(len(Applymap('fieldmap',Name,ID))>0,

and this gives me blank values

Re: Applymap to fill values

raji6763 — Fri, 19 Jun 2020 08:48:59 GMT

hi ,

here is the solution

try the below condition in script(note:Change your map table name)

If(Len(ApplyMap('FillmapTableB',Name))<>0,ApplyMap('FillmapTableB',Name,ID),ID) As ID

Output:

Regards,

raji