Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
May be this:
[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 ([Emerging_Market_Data])
LOAD em_country_in as erm_mrkt_country,
is_em_country_in as is_erm_mrkt
RESIDENT emerging_market_countries;
[Emerging_Market_Data1]:
NoConcatenate
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 ([Emerging_Market_Data1])
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;
Concatenate ([Emerging_Market_Data])
LOAD *
Resident [Emerging_Market_Data1];
DROP Table [Emerging_Market_Data1];
try this
[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([Emerging_Market_Data])
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;
Please Try
[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([Emerging_Market_Data])
LOAD em_country_in as erm_mrkt_country,
is_em_country_in as is_erm_mrkt
RESIDENT emerging_market_countries;
CONCATENATE
[Emerging_Market_Data1]:
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([Emerging_Market_Data1])
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;
hth
Sasi
This did not work but thanks.
This fails to load with a Table Not Found: on the LEFT JOIN(Emerging_Market_Data1]) line. But thanks!
May be this:
[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 ([Emerging_Market_Data])
LOAD em_country_in as erm_mrkt_country,
is_em_country_in as is_erm_mrkt
RESIDENT emerging_market_countries;
[Emerging_Market_Data1]:
NoConcatenate
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 ([Emerging_Market_Data1])
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;
Concatenate ([Emerging_Market_Data])
LOAD *
Resident [Emerging_Market_Data1];
DROP Table [Emerging_Market_Data1];
Try using a mapping load
map:
mapping load em_country_in
is_em_country_in
RESIDENT emerging_market_countries;
[Emerging_Market_Data1]:
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,
applymap('map',erm_tmp2_country,'NA') as is_erm_mrkt
RESIDENT emerging_market_data_temp2
WHERE erm_tmp2_country <> 'United States';
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,
applymap('map',erm_tmp2_country,'NA') as is_erm_mrkt
RESIDENT emerging_market_data_temp2
WHERE erm_tmp2_country = 'United States';
hth
Sasi
Yes, this works. I was hoping for something more compact, but this does work. Thanks!
This is an interesting approach. I'll have to try it. Thanks!
I wish there was a easier way to do a join and concatenates (may be using parenthesis), but I doubt it will work. Although I have never tried it and it has made me curious to try it now. I will get back to you if I parenthesis work.
Best,
Sunny