Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concatenating data with joins

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;

1 Solution

Accepted Solutions
sunny_talwar

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];

View solution in original post

11 Replies
gautik92
Specialist III
Specialist III

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;

sasiparupudi1
Master III
Master III

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

Not applicable
Author

This did not work but thanks.

Not applicable
Author

This fails to load with a Table Not Found: on the LEFT JOIN(Emerging_Market_Data1]) line.  But thanks!

sunny_talwar

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];

sasiparupudi1
Master III
Master III

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

Not applicable
Author

Yes, this works.  I was hoping for something more compact, but this does work.  Thanks!

Not applicable
Author

This is an interesting approach.  I'll have to try it.  Thanks!

sunny_talwar

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