4 Replies Latest reply: Dec 20, 2011 4:18 PM by John Witherspoon RSS

    Data load

    Josh Duenyas

      Hi all

       

      Is it possible to link 2 already loaded tables and create a third table?

      The reason is taht one of the tables requires long time to load and I don't want to call it again for another load.

       

      Here is a simplified case:

       

      TableA:

      Load OrderID,

             customerName,

             QuoteID;

      SELECT *

      FROM ORDERS;

       

      TableB:

      Load QuoteDate,

             Amount,

             QuoteID;

      SELECT *

      FROM QUOTES;

       

      And now what should the structure be to create TableC?

       

      TableC:

      Load CustomerName,

             QuoteDate,

             Amount;

      Resident TableA INNER JOIN Resident TableB ON TableA.QuoteID = TableB.QuoteID (???)

       

       

      Thanks

        • Data load
          Lucian Cotea

          TableC:

          noconcatenate LOAD

             CustomerName,

              QuoteID

          RESIDENT TableA;

           

          inner join (TableC)

          LOAD

              QuoteID,

              Amount

          RESIDENT TableB;

          • Re: Data load
            John Witherspoon

            If you do nothing, tables A and B will be automatically connected by QuoteID.  No need to load table C.  If you really want them in a single table:

             

            TableC:
            Load OrderID,
                   customerName,
                   QuoteID;
            SELECT *
            FROM ORDERS;

             

            INNER JOIN (TableC)
            Load QuoteDate,
                   Amount,
                   QuoteID;
            SELECT *
            FROM QUOTES;

             

            DROP FIELD QuoteID;

             

            Edit: And I mean do this INSTEAD of loading tables A and B.

              • Data load
                Josh Duenyas

                Thank you Jon for your reply.

                I follow your positings and figuered I will "hear" from you.

                 

                Alas, I do need the other 2 tables on their own and the 3rd one should be separated. The structure of my data (currently some 35 different tables from many complex views in SQL) are too complex to wade and weed through.

                 

                When time is right I believe I should revisit the whole structure and do the right things to it.

                 

                Thanks anyways.

                  • Re: Data load
                    John Witherspoon

                    jduenyas wrote:

                     

                    Alas, I do need the other 2 tables on their own and the 3rd one should be separated.


                    OK, then do what Lucian suggested, but rename the fields as you load them in, or use QUALIFY to do it automatically.  Otherwise, all the fields will connect to each other since they have the same names.