3 Replies Latest reply: Aug 4, 2016 1:21 PM by M Healey RSS

    Help with joins

    M Healey

      Hi Everyone, I've got 5 tables. One complete table, two summary tables, and two detail tables. I'd like to get these all into one table.

       

      Table 1 has columns A B C D E

       

      Table 2 (summary data) has columns A B C

      Table 3 (detail data) has columns A D E

       

      Table 4 (summary data) has columns A B C

      Table 5 (details data) has columns A D E

       

      I tried the code below thinking that they would auto-concatenate, but they did not. Is there a solution for this?

       

      LOAD a, b, c from table2.csv;

      join LOAD a, d, e from table3.csv;

       

      LOAD a, b, c from table4.csv;

      join LOAD a, d, e from table5.csv;

       

      LOAD a, b, c, d, e from table1.csv;

        • Re: Help with joins
          Mat Smith

          Rather than hoping for the tables to 'auto-concatenate', why not force them to yourself?

           

          Table1:

          LOAD a, b, c, d, e from table1.csv

           

          concatenate (Table1)

          LOAD a, b, c from table2.csv

           

          concatenate (Table1)

          LOAD a, d, e from table3.csv

           

          concatenate (Table1)

          LOAD a, b, c from table4.csv

           

          concatenate (Table1)

          LOAD a, d, e from table5.csv

           

          Hope this helps,

           

          Mat

          • Re: Help with joins
            Miguel Braga

            Hi there,

             

            Try something like this:

             

            Table2:

            LOAD a, b, c from table2.csv;

             

            left join

            LOAD a, d, e from table3.csv;

             

            Table3:

            LOAD a, b, c from table4.csv;

             

            left join

            LOAD a, d, e from table5.csv;

             

            Concatenate

             

            Table1:

            LOAD a, b, c, d, e from table1.csv;

             

             

            FinalTable:

            NoConcatenate

            LOAD * Resident Table2;

             

            left join (FinalTable)

            LOAD * Resident Table3;

             

            Drop Table Table2;

            Drop Table Table3;

             

            Rename Table FinalTable to Table;

             

             

            Hope this helps you

             

            Regards,

            MB