11 Replies Latest reply: Mar 21, 2017 6:17 AM by Supriya R RSS

    Combining two tables data into single table

    Supriya R

      Hi,

       

      i am trying to append/combine  both table data and put that to one single table using below code

       

      let varExpirationDate  = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');

      Welcome:

      LOAD `campaign_id`,

          `batch_meta_data_id`,

          `campaign_name`,

          `start_date`,

          `expiration_date` Where Match(batch_meta_data_id,'88','23','24') and expiration_date < '$(varExpirationDate)';

      SQL SELECT `campaign_id`,

          `batch_meta_data_id`,

          `campaign_name`,

          `start_date`,

          `expiration_date`

      FROM campaign ;

       

       

      NoConcatenate

      MainTable:

      LOAD

      batch_meta_data_id as last_batch,

      MAX(campaign_id) as campaign_id

      Resident Welcome  Group By batch_meta_data_id Order By batch_meta_data_id;

      Left Join

      LOAD  campaign_id,

      batch_meta_data_id,

      `campaign_name`,

      `start_date`,

      `expiration_date`

      Resident Welcome;

      Drop Table Welcome;

       

      Welcome1:

      LOAD `campaign_id`,

          `batch_meta_data_id` ,

          `campaign_name` ,

          `start_date` ,

          `expiration_date` Where Match(batch_meta_data_id,'88','23','24') and expiration_date >= '$(varExpirationDate)';

      SQL SELECT `campaign_id`,

          `batch_meta_data_id`,

          `campaign_name`,

          `start_date`,

          `expiration_date`

      FROM campaign;

       

      These scripts create synthetic table. How to avoid this and get one single table?

      Is there any other way i could achieve same with less code?

      Please help me on this. Any suggestion and idea is appreciated.

        • Re: Combining two tables data into single table
          AC BC

          Is there any base Sql query on which you are doing this or try to explain what you want to get by giving simple table name like A, B and C

          • Re: Combining two tables data into single table
            Sarvesh Srivastava

            Write your code like this:

             

            Response:

            LOAD RID,

                Q001,

                Q002,

                Q003,

                Q004,

                Q005;

            SQL SELECT *

            FROM Response;

             

            LOAD QID,

                `Option_Value`,

                `Option_Text`,

                `Is_Excluded`;

            SQL SELECT *

            FROM `Question_option`;

             

            Response_Final:

            CrossTable(QID, Values)

            Load *

            resident Response;

             

             

            Drop Table Response;

            • Re: Combining two tables data into single table
              Dave Riley

              I think the key is to load all your data from 'campaign' and then left join to the full table, but only to fields with the expiration_date before your variable, so you need to 'flag' that data, something like ...

               

               

              let varExpirationDate  = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');

              MainTable:
              LOAD `campaign_id`,
              `batch_meta_data_id`,
              `campaign_name`,
              `start_date`,
              `expiration_date`,
              if(expiration_date < '$(varExpirationDate)',1,0) as expFlag
              Where Match(batch_meta_data_id,'88','23','24');
              SQL SELECT `campaign_id`,
              `batch_meta_data_id`,
              `campaign_name`,
              `start_date`,
              `expiration_date`
              FROM campaign ;

              left join (MainTable)
              LOAD
              MAX(campaign_id) as campaign_id,
              1
              as expFlag,
              batch_meta_data_id as last_batch
              Resident MainTable
              Group By batch_meta_data_id;

               

              flipside

                • Re: Combining two tables data into single table
                  Supriya R

                  Assume i have data like below

                   

                       

                  batch_meta_data_idcampaign_idcampaign_namestart_dateexpiration_date
                  12as02-02-201723-03-2017
                  13ds25-02-201722-03-2017
                  14fd12-02-201716-03-2017
                  15sa12-02-201718-03-2017
                  16sa12-02-201717-03-2017

                   

                  i want to get only these

                   

                  batch_meta_data_idcampaign_idcampaign_namestart_dateexpiration_date
                  12as02-02-201723-03-2017
                  13ds25-02-201722-03-2017
                  15sa12-02-201718-03-2017

                   

                  Here first two are having expiration_date >= today, and third record is the top one record(i.e lastest expired record) where expiration_date < today.

                  Let me know if you need more clarification

                   

                  I need to get data like this for all batch_meta_data_id.

                   

                  Please help me on this.

                    • Re: Combining two tables data into single table
                      Rajesh RS

                      Hi Supriya,

                       

                      You can try with two temporary tables..

                       

                      Fill the first table with first condition and fill the second table with second condition.

                      Then combine/concatenate both the tables.

                      You will get the output..

                       

                       

                      Regards,

                      Rajesh R. S.

                      • Re: Combining two tables data into single table
                        Dave Riley

                        Okay, try with this code. It probably needs more testing, though. The 'left join' needed to find the max expiration_date rather than batch_meta_data_id, and the end result moved to a new table to filter out the redundant data.

                         

                         

                        let varExpirationDate  = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');

                        MainTable:
                        Load *, if(expiration_date < '$(varExpirationDate)',1,0) as expFlag inline [
                        batch_meta_data_id, campaign_id, campaign_name, start_date, expiration_date
                        1, 2, as, 02-02-2017, 23-03-2017
                        1, 3, ds, 25-02-2017, 22-03-2017
                        1, 4, fd, 12-02-2017, 16-03-2017
                        1, 5, sa, 12-02-2017, 18-03-2017
                        1, 6, sa, 12-02-2017, 17-03-2017
                        ]
                        ;

                        // MainTable:
                        // LOAD `campaign_id`,
                        // `batch_meta_data_id`,
                        // `campaign_name`,
                        // `start_date`,
                        // `expiration_date`,
                        // if(expiration_date < '$(varExpirationDate)',1,0) as expFlag
                        // Where Match(batch_meta_data_id,'88','23','24');
                        // SQL SELECT `campaign_id`,
                        // `batch_meta_data_id`,
                        // `campaign_name`,
                        // `start_date`,
                        // `expiration_date`
                        // FROM campaign ;

                        left join (MainTable)
                        LOAD
                        MAX(expiration_date) as expiration_date,
                        '1'
                        as expFlag,
                        batch_meta_data_id as last_batch
                        Resident MainTable
                        where expFlag = 1
                        Group By batch_meta_data_id;

                        EndTable:
                        Noconcatenate Load * resident MainTable where last_batch = 1 OR expFlag = 0;

                        Drop Table MainTable;

                         

                        flipside