Apply Map replace Field Values

[qlikwiz123]

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	7
F	8
I	10
J	11
K	15

Result:

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

I want to replace and fill the null ID values from Table B in Table A.

NameIdMap:
Mapping Load Name, ID 
From TableB

Table_A:
Load Name,
if(len(ID) > 0, ID, ApplyMap('NameIdMap', Name)) as ID
From TableA

I tried this but this does not replace ID values in TableA with the values in Table B

[lironbaram]

Hi 

in your result table you  replaced existing values of ID , with values from the mapping table 
but your expression is keeping existing ID , and only fill missing , what exactly you look for 
any how 
if you want to replace existing and fill the field ID1 in the script below will do it , 
and if you only want to fill the missing value than ID2 is your choice

fieldmap:
Mapping load * inline [
Name,ID
A,20
B,2
C,99
D,9
E,7
F,8
I,10
J,11
K,15
];



Table1:
load *,
	Applymap('fieldmap',Name,ID) 			as ID1,
    if(ID>0,ID,Applymap('fieldmap',Name) ) 	as ID2;
load * Inline [
Name,ID
A,1
B,	 
C,5
D,	 
E,7
L,10
];

[qlikwiz123]

Doesn't seem to be working. Returns empty values for the missing ones and does not replace the IDs either.

[Saravanan_Desingh]

TableA's A, C & E are not NULL. Why are still replacing it from TableB? Do u want to replace always with B value?

[qlikwiz123]

Yes. If Table A ID is null, then fill it with values from Table B. If Table A ID has value, then it must be replaced with Table B's value. If Table A ID has value and Table B does not, then fill the value from Table A

[Saravanan_Desingh]

Check this.

Mapper:
Mapping
LOAD * INLINE [
    Name, ID
    A, 20
    B, 2
    C, 99
    D, 9
    E, 7
    F, 8
    I, 10
    J, 11
    K, 15
];

tab1:
LOAD Name, ApplyMap('Mapper',Name,ID) As ID INLINE [
    Name, ID
    A, 1
    B,  
    C, 5
    D,  
    E, 7
    Z, 88
];

[Saravanan_Desingh]

Output

[qlikwiz123]

@Saravanan_Desingh 

Hi,

This fails my 3rd condition

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.

[Saravanan_Desingh · Accepted solution]

Can u try this?

Mapper:
Mapping
LOAD * INLINE [
    Name, ID
    A, 20
    B, 2
    C, 99
    D, 9
    E, 
    F, 8
    I, 10
    J, 11
    K, 15
];

tab1:
LOAD Name, If(Len(ApplyMap('Mapper',Name))<>0,ApplyMap('Mapper',Name,ID),ID) As ID INLINE [
    Name, ID
    A, 1
    B,  
    C, 5
    D,  
    E, 7
    Z, 88
];

[Saravanan_Desingh]