Overwrite with value from another table if exists

[njimack]

Hi all

My first time posting - apologies if this has been answered previously but I've trawled through loads of posts and can't find an answer.

Within my app I have 2 tables - the first lists some countries and a specific GBL.  The second has a longer list of Sites and GBLs, as well as numerous other fields.

Table A:

Site	GBL
Belgium	CMB
Czech Republic	CMB
Ireland	CMB
Luxembourg	GBM
Netherlands	CMB
Malta	CMB
Switzerland	CMB

Table B:

Site	GBL	Product_Code	Balance_USD
Belgium	CMB	OAO	38.55504172
Belgium	CMB	NCA	3479.940877
Belgium	GBM	NCA	33235.77782
Belgium	GBM	NC3	431053.9024
Belgium	GBM	CUR	717168.6868
Bermuda	CMB	TDC	25126.75
Bermuda	CMB	QBS	63140.72
Bermuda	CMB	TDQ	63171.47
Bermuda	GBM	YYY	1432.6
Bermuda	GBM	OCB	19667.15172
Bermuda	GBM	AOC	143557.61
Czech Republic	CMB	CN8	86.7785095
Czech Republic	CMB	CA2	500.6670304
Czech Republic	CMB	OCB	1222.55653
Czech Republic	GBM	SV1	0.910715594
Czech Republic	GBM	CN6	37.74032606
Czech Republic	GBM	CUA	55.27862417

If a site exists in Table A, then I want all 'GBL's in Table B to be overwritten with the value from Table A.  If it doesn't exist, it should remain as is.

I've tried combinations of ApplyMap and WHERE EXISTS but can't get it to work.

Any assistance gratefully received!

[Taoufiq_Zarra · Accepted solution]

@njimack  if I understood correctly you need :

applymap('Map1',Site,GBL) as GBL

full script :

Map1:
mapping
LOAD * INLINE [
    Site, GBL
    Belgium, CMB
    Czech Republic, CMB
    Ireland, CMB
    Luxembourg, GBM
    Netherlands, CMB
    Malta, CMB
    Switzerland, CMB
];

TableB:
LOAD Site,applymap('Map1',Site,GBL) as GBL, Product_Code, Balance_USD INLINE [
    Site, GBL, Product_Code, Balance_USD
    Test, Test, OAO, 38.55504172
    Belgium, CMB, NCA, 3479.940877
    Belgium, GBM, NCA, 33235.77782
    Belgium, GBM, NC3, 431053.9024
    Belgium, GBM, CUR, 717168.6868
    Bermuda, CMB, TDC, 25126.75
    Bermuda, CMB, QBS, 63140.72
    Bermuda, CMB, TDQ, 63171.47
    Bermuda, GBM, YYY, 1432.6
    Bermuda, GBM, OCB, 19667.15172
    Bermuda, GBM, AOC, 143557.61
    Czech Republic, CMB, CN8, 86.7785095
    Czech Republic, CMB, CA2, 500.6670304
    Czech Republic, CMB, OCB, 1222.55653
    Czech Republic, GBM, SV1, 0.910715594
    Czech Republic, GBM, CN6, 37.74032606
    Czech Republic, GBM, CUA, 55.27862417
];

output:

[njimack]

@Taoufiq_Zarra  Perfect - many thanks!