4 Replies Latest reply: Mar 11, 2017 8:24 PM by 尾崎 量也 RSS

    Table integration

    尾崎 量也

      Hello!

      I want to integrate 2 tables below, and get the integrated table.

      I would appreciated if you could tell me a sample of load script.

       

      [table1]

      A, B

      1, 2

      2, 2

      [table2]

      A, C

      3, 3

      3, 4

      [Integrated table]

      A, B, C

      1, 2, -

      2, 2, -

      3, -, 3

      3, -, 4

       

      Thanks in advance.

        • Re: Table integration
          Gysbert Wassenaar

          You can use the CONCATENATE keyword to force concatenation of records from a source table to a table created earlier in the script.

           

          [Integrated table]:

          LOAD  A, B FROM ...somewhere...;

           

          CONCATENATE ([Integrated table])

           

          LOAD A,C FROM ...somewhere else...;

            • Re: Table integration
              尾崎 量也

              Thank you so much for your quick response!!

              As your script, I tried, but I have got an error"Table AA not found".

              Could you give me more advice??

               

              the following is my script.

              ---------------

              AA:
              Load *
              Resident A;

              CONCATENATE([AA])

              Load *
              Resident B;

                • Re: Table integration
                  Ruben Marin

                  Hi, seems that you are previously loading table 'A', with the same fields as 'AA' (because the use of '*'), so it's autoconcatenating in table 'A', to avoid this you can use 'NoConcatenate':

                   

                  AA:

                  NoConcatenate
                  Load *
                  Resident A;

                  CONCATENATE([AA])

                  Load *
                  Resident B;

                   

                  If you keep all tables it will create a lot of syntethic keys (tables joined by more than one field), maybe you want something like:

                  Concatenate (A)

                  LOAD * Resident B;

                  DROP Table B;

                   

                  Or try renaming fields in 'AA' table to avoid unwanted keys.