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:
NoConcatenate Keytable: Load Distinct *, Entity&'|'&Code&'|'&"Year" as Key1, Entity&'|'&Code&'|'&"Year" as Key2, Region&'|'&"Year" as Key3, Region&'|'&"Year" as Key4, Entity&'|'&Code&'|'&"Year" as Key5, Country&'|'&"Year" as Key6, "Country Name"&'|'&"Country Code"&'|'&"Year" as Key7, "Year" as key8, CountryCode&'|'&"Year" as Key9, CountryCode&'|'&"Year" as Key10 Resident tmp_keytable; Drop Table tmp_keytable;
@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.
@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.
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?
