Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
MVP
MVP

Re: Concatenating data with joins

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

11 Replies
gautik92
Valued Contributor III

Re: Concatenating data with joins

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
Honored Contributor III

Re: Concatenating data with joins

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

Re: Concatenating data with joins

This did not work but thanks.

Not applicable

Re: Concatenating data with joins

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

MVP
MVP

Re: Concatenating data with joins

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
Honored Contributor III

Re: Concatenating data with joins

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

Re: Concatenating data with joins

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

Not applicable

Re: Concatenating data with joins

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

MVP
MVP

Re: Concatenating data with joins

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

Community Browser