topic Re: Overwrite with value from another table if exists in App Development

Overwrite with value from another table if exists

njimack — Mon, 20 Dec 2021 21:08:46 GMT

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!

Re: Overwrite with value from another table if exists

Taoufiq_Zarra — Mon, 28 Sep 2020 10:40:03 GMT

@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:

Re: Overwrite with value from another table if exists

njimack — Mon, 28 Sep 2020 12:52:59 GMT

@Taoufiq_Zarra Perfect - many thanks!