Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table
Table A:
Name | ID |
A | 1 |
B | |
C | 5 |
D | |
E | 7 |
Table B:
Name | ID |
A | 1 |
B | 2 |
C | 0 |
D | 9 |
E | 7 |
F | 8 |
I | 10 |
J | 11 |
K | 15 |
Result:
Name | ID |
A | 1 |
B | 2 |
C | 5 |
D | 9 |
E | 7 |
How do I join Table B with Table A to fill the missing 'ID' values in Table A? I tried Left Join but it is not working. Also tried Where Not Exist (ID).
I DON'T want to re-write the values that are already present in Table A, but only fill the missing values from Table B. So Left - Join is not ideal in this case
NameIdMap:
Mapping Load Name, ID
From....SourceB
Table_A:
Load Name,
if(len(ID) > 0, ID, ApplyMap('NameIdMap', Name)) as ID
From ...SourceA
-Rob
A left join won't work because TableA already contains an ID column. There are a couple of solutions depending on the structure of your script. One straightforward way is to not load the ID field into TableA initially. Then a Left Join from TableB will work. This assumes that TableB has all the values you need, not just the missing ones.
-Rob
Hi @rwunderlich
I need to keep ID field values in Table A as there might be instances where Table B may have different ID Field Values.
I only need to fill the gaps for the ID field in Table A. Please suggest assuming the script is simple as below.
Table_A:
Load Name, ID
From ...SourceA
Table B:
Load Name, ID
From....SourceB
hi,
as rob suggest left join not work because ID field already present in 1st table so either you have rename it or do not consider it.
try below
Table_A:
Load Name
From ...SourceA
Left Join
Load Name, ID
From....SourceB
Regards,
max
Hi,
I don't want to replace the values of Field ID from Table A. Instead fill the gaps from Table_B. Doing a left Join will attach 'ALL' values from ID field in Table B, which should be avoided.
ok in such case you can applymap() ..
Check below thread
Regards,
Max
NameIdMap:
Mapping Load Name, ID
From....SourceB
Table_A:
Load Name,
if(len(ID) > 0, ID, ApplyMap('NameIdMap', Name)) as ID
From ...SourceA
-Rob
Perfect! Thank you so much @rwunderlich and @PrashantSangle
Quick follow up question @rwunderlich
How can I leave the ID field blank in this expression if(len(ID) > 0, ID, ApplyMap('NameIdMap', Name)) as ID
if the Name is not found in SouceB (NameIdMap in this load)
The optional third parameter to ApplyMap() is a default value. You can specify it there.
-Rob