Data load

[jduenyas]

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

[luciancotea]

TableC:

noconcatenate LOAD

   CustomerName,

    QuoteID

RESIDENT TableA;

inner join (TableC)

LOAD

    QuoteID,

    Amount

RESIDENT TableB;

[johnw]

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.

[jduenyas]

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.

[johnw]

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.