11 Replies Latest reply: Sep 28, 2015 6:08 PM by Mark Ford RSS

    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;

        • Re: Concatenating data with joins
          Gowtham Kesavan

          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;

          • Re: Concatenating data with joins
            Sasidhar Parupudi

            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

              • Re: Concatenating data with joins

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

                • Re: Concatenating data with joins
                  Sasidhar Parupudi

                  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

                • Re: Concatenating data with joins
                  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];