Load From one table by values from another

[#1]

Hello all,

Let's say I have these 2 tables I'm loading from. Table 1 contains sales orders, and Table 2 contains item information.

Table 1 has an order with Items 123, 234, and 456. Table 2 has those items but a bunch more as well. The goal would be to load ALL records from Table 1, and only load the records from Table 2 where those item numbers match the sales orders from Table 1.

This is probably relatively easy to do but I can't seem to get it working right.

Thank you!

[alexandros17]

Table 1 ....

Left join

Table 2

in this way only values for table 2 corresponding to table 1 will be loaded

hope it hrlps

[#3]

By the way, I should have clarified I know I can do a left join or a left keep, but is there a more efficient way to do the load?

[Gysbert_Wassenaar · Accepted solution]

You can use left join or left keep depending on if you want one resulting table or two separate tables.

Table1:

Load * from ...sourcetable1;

Table2:

left keep

load * from ...sourcetable2;

[shree909]

Hi

I think

u need to make the left join

Example:

Table1:

load sales_order,

item

from  file or database

left join(Table1)

load

item

from  database or flatfile

by doing this u will get all the records from table 1 and only matching records from table2.

..

Hope this helps

[#6]

Hi,

You can also use exists() function for the same. Refer attached sample file.

SalesOrder:

LOAD * INLINE [

    OrderNumber

    123

    234

    456

];

SalesDetails:

LOAD * INLINE [

    OrderNumber, OrderDetailNo, Amount

    123, 1 , 100

    234, 2 , 200

    456, 3, 800

    567, 5, 200

    678, 6, 600

    123, 2, 500

]

where exists(OrderNumber);

Regards,

Rahul