error message when creating a link table

chrisjad — Sat, 23 Jul 2022 12:28:06 GMT

Hello all,

I am trying to create a link table which will have all the composite keys from the different fact tables.

However, I m getting an error message: The following error occurred: Field 'Region' not found. The error occurred here: NoConcatenate......

Somehow, I can't understand the reason.

Could someone help me with this issue?

Below is my script:

LOAD:

Annual_Forest_Expansion:
LOAD Entity&'|'&Code&'|'&"Year" as Key1,
Entity,
Code,
"Year",
"Forest expansion"
FROM [lib://DataFiles/annual-forest-expansion.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Forest_Area_Share_of_Land_Area:
LOAD Entity&'|'&Code&'|'&"Year" as Key2,
Entity,
Code,
"Year",
"Forest cover"
FROM [lib://DataFiles/forest-area-as-share-of-land-area.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

EU_paperboard_production:
LOAD Region&'|'&"Year" as Key3,
Region as Entity,
"Year",
Value as ValuePP
FROM [lib://DataFiles/Paper_paperboard_production_unpivoted.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

Belgium_meat_consuption:
LOAD Region&'|'&"Year" as Key4,
Region as Entity,
"Year",
Value as ValueMC
FROM [lib://DataFiles/BelgiumMeatConsuption_unpivoted.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

Net_deforestation_in_trade:
LOAD Entity&'|'&Code&'|'&"Year" as Key5,
Entity,
Code,
"Year",
net_traded_deforestation
FROM [lib://DataFiles/net-deforestation-in-trade (1).csv]
(txt, codepage is 28592, embedded labels, delimiter is ',', msq);

EU_buildings:
LOAD Country&'|'&"Year" as Key6,
Country as Entity,
"Year",
"Country code" as country_code_alpha2,
Value as ValueBuild
FROM [lib://DataFiles/eu-buildings-unpivoted.csv]
(txt, codepage is 28592, embedded labels, delimiter is ';', msq);

Poverty_gap_at_1.90:
LOAD "Country Name"&'|'&"Country Code"&'|'&"Year" as Key7,
"Country Name" as Entity,
"Country Code" as Code,
"Year",
"Indicator Name",
"Indicator Code",
Value as ValuePov
FROM [lib://DataFiles/Poverty gap at 1.90.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

Tree_cover_loss_by_dominent_drivers:
LOAD
"Year" as key8,
"Year",
DriverType,
TreeCoverLoss_ha as TreeCoverLoss_haDr,
GrossEmissions_Co2_all_gases_Mg as GrossEmissions_Co2_all_gases_MgDr
FROM [lib://DataFiles/TreeCoverLoss_2001-2020_DominantDrivers.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Tree_cover_loss_by_region:
LOAD CountryCode&'|'&"Year" as Key9,
CountryCode as Code,
"Year",
TreeCoverLoss_ha as TreeCoverLoss_haRe,
GrossEmissions_Co2_all_gases_Mg as GrossEmissions_Co2_all_gases_MgRe
FROM [lib://DataFiles/TreeCoverLoss_2001-2020_ByRegion.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Tree_cover_loss_in_primary_forest:
LOAD CountryCode&'|'&"Year" as Key10,
CountryCode as Code,
"Year",
TreeCoverLoss_ha as TreeCoverLoss_haPF,
GrossEmissions_Co2_all_gases_Mg as GrossEmissions_Co2_all_gases_MgPF
FROM [lib://DataFiles/TreeCoverLoss_2001-2020 _InPrimaryForest.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Temp Table:

tmp_keytable:
load distinct
Entity,
Code,
"Year"
FROM [lib://DataFiles/annual-forest-expansion.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Concatenate(tmp_keytable)
load Distinct
Entity,
Code,
"Year"
FROM [lib://DataFiles/forest-area-as-share-of-land-area.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Concatenate(tmp_keytable)
load Distinct
Region as Entity,
"Year"
FROM [lib://DataFiles/Paper_paperboard_production_unpivoted.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

Concatenate(tmp_keytable)
load Distinct
Region as Entity,
"Year"
FROM [lib://DataFiles/BelgiumMeatConsuption_unpivoted.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

Concatenate(tmp_keytable)
load Distinct
Entity,
Code,
"Year"
FROM [lib://DataFiles/net-deforestation-in-trade (1).csv]
(txt, codepage is 28592, embedded labels, delimiter is ',', msq);

Concatenate(tmp_keytable)
load Distinct
Country as Entity,
"Year"
FROM [lib://DataFiles/eu-buildings-unpivoted.csv]
(txt, codepage is 28592, embedded labels, delimiter is ';', msq);

Concatenate(tmp_keytable)
load Distinct
"Country Name" as Entity,
"Country Code" as Code,
"Year"
FROM [lib://DataFiles/Poverty gap at 1.90.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

Concatenate(tmp_keytable)
load Distinct
"Year"
FROM [lib://DataFiles/TreeCoverLoss_2001-2020_DominantDrivers.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Concatenate(tmp_keytable)
load Distinct
CountryCode as Code,
"Year"
FROM [lib://DataFiles/TreeCoverLoss_2001-2020_ByRegion.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Concatenate(tmp_keytable)
load Distinct
CountryCode as Code,
"Year"
FROM [lib://DataFiles/TreeCoverLoss_2001-2020 _InPrimaryForest.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Key Table:

Re: error message when creating a link table

Lisa_P — Sun, 24 Jul 2022 23:07:07 GMT

There is no region field loaded, you have renamed Region as Entity in your previous load statements

Re: error message when creating a link table

sidhiq91 — Mon, 25 Jul 2022 04:53:12 GMT

@chrisjad adding to what @Lisa_P mentioned I see even the country has been renamed to Entity could you please correct them? Let me know if you need any further help on this.

Re: error message when creating a link table

chrisjad — Mon, 25 Jul 2022 09:57:30 GMT

It seems I forgot to drop the fields. It is now working

Re: error message when creating a link table

chrisjad — Mon, 25 Jul 2022 10:48:36 GMT

Hi again @Lisa_P and @sidhiq91

In my link table, I now have fields that are the same but with different name. For example, in the link table, I have the fields Entity, Region, Country and Country Name (from different fact tables) that are the same.

As I have not renamed them, is there a way to merge these fields in one single column in my link table?

topic Re: error message when creating a link table in Connectivity & Data Prep

error message when creating a link table

Re: error message when creating a link table

Re: error message when creating a link table

Re: error message when creating a link table

Re: error message when creating a link table