I'm trying to load part of a dataset while merging in data from another dataset with a LEFT JOIN then loading the rest of the original dataset while merging in data from a third dataset with another LEFT JOIN and concatenate each set together. This almost works except it isn't merging in the third set of data (the stuff after the second LEFT JOIN near the end). Any ideas?
[Emerging_Market_Data]:
LOAD erm_tmp2_id as erm_mrkt_id,
erm_tmp2_country as erm_mrkt_country,
erm_tmp2_state_abbrev as erm_mrkt_state,
erm_tmp2_current_industry as erm_mrkt_industry,
erm_tmp2_hp_product as erm_mrkt_product
RESIDENT emerging_market_data_temp2
WHERE erm_tmp2_country <> 'United States';
LEFT JOIN
LOAD em_country_in as erm_mrkt_country,
is_em_country_in as is_erm_mrkt
RESIDENT emerging_market_countries;
CONCATENATE
LOAD erm_tmp2_id as erm_mrkt_id,
erm_tmp2_country as erm_mrkt_country,
erm_tmp2_state_abbrev as erm_mrkt_state,
erm_tmp2_current_industry as erm_mrkt_industry,
erm_tmp2_hp_product as erm_mrkt_product
RESIDENT emerging_market_data_temp2
WHERE erm_tmp2_country = 'United States';
LEFT JOIN
LOAD em_state_in as erm_mrkt_state,
em_state_name_in as erm_mrkt_state_name,
is_em_state_in as is_erm_mrkt
RESIDENT emerging_market_states;
