Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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
];
Doesn't seem to be working. Returns empty values for the missing ones and does not replace the IDs either.
TableA's A, C & E are not NULL. Why are still replacing it from TableB? Do u want to replace always with B value?
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
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
];
Output
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.
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
];