Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
njimack
Contributor III
Contributor III

Overwrite with value from another table if exists

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:

SiteGBL
BelgiumCMB
Czech RepublicCMB
IrelandCMB
LuxembourgGBM
NetherlandsCMB
MaltaCMB
SwitzerlandCMB

 

Table B:

SiteGBLProduct_CodeBalance_USD
BelgiumCMBOAO38.55504172
BelgiumCMBNCA3479.940877
BelgiumGBMNCA33235.77782
BelgiumGBMNC3431053.9024
BelgiumGBMCUR717168.6868
BermudaCMBTDC25126.75
BermudaCMBQBS63140.72
BermudaCMBTDQ63171.47
BermudaGBMYYY1432.6
BermudaGBMOCB19667.15172
BermudaGBMAOC143557.61
Czech RepublicCMBCN886.7785095
Czech RepublicCMBCA2500.6670304
Czech RepublicCMBOCB1222.55653
Czech RepublicGBMSV10.910715594
Czech RepublicGBMCN637.74032606
Czech RepublicGBMCUA55.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!

1 Solution

Accepted Solutions
Taoufiq_Zarra

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

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra

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

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
njimack
Contributor III
Contributor III
Author

@Taoufiq_Zarra  Perfect - many thanks!