left join after concatenating table

[that_anonymous_guy]

I want join my data as below but my this join is not considering the concatenated part Join is only working till <src2>

Table 1:

load 

A,

B

from <src1>;

left join(Table 1)

Table 2:

load

C as A,

D

from <src2>;

concatenate

load

E as A,

F as D

from <src3>

Can anyone tell me what I am doing and how can I achieve this?

[henrikalmen]

In the last load, try specifying table name for your concatenation, like this:
concatenate (Table1) load E as A, F as D from <src3>;

[that_anonymous_guy]

still dosen't work...what I want is to have concatenation first and then join Table 1 and Table 2(which will already contain the concatenated rows now).

What it's doing now is joining C,D first and then concatenating E F to the entire table

[henrikalmen]

It sounds that you should just change the order of things?

Load Table1 from src1, concatenate load src3 to table1 (so you will still have only one table named Table1), and then join src2 to Table1.

Or am I misunderstanding what you want to do?

[vinieme12 · Accepted solution]

Left Join will execute before the tables get concatenated, change the order and use a RIGHT JOIN instead

Table2:

load

C as A,

D

from <src2>;

concatenate(Table2)

load

E as A,

F as D

from <src3>

RIGHT JOIN(Table2)

Table 1:

load 

A,

B

from <src1>;

[henrikalmen]

Why would a left join execute before concatenation in the example given in the original question? Left join should make sure everything in the table joined to ("table2") in your example is kept. A right join will make sure everything in the joining source table (src3) is available in the result, but rows that doesn't match in the originating table is dropped.

[henrikalmen]

It sounds like you should first concatenate, then join. But then you will have problems with column names. A concatenation adds rows to a table, joining adds columns.

This is what happens in your example:

//Table 1 is created, with columns A and B:
Table 1: load A, B from <src1>;

//Table1 gets a new column D where the value of column C in src2 matches the value of column A in Table1:
left join(Table1) load C as A, D from <src2>;

//all rows from src3 is added to Table1, with values in column A and D:
concatenate (Table1) load E as A, F as D from <src3>;

Maybe this is what you want:

//load Table2, it will have the columns A and D:
Table2: load C as A, D <src2>;

//add rows from src3, rows are added but columns stay the same:
concatenate(Table2) load E as A, F as D from <src3>;

//now add the column B from src1 on the rows where the value of column A in Table2 matches the value of column A in src1:
left join(Table2) load A, B from <src1>;
//(if you do right join instead, all rows from src1 will be available in the result, but rows in Table2 where column A did not match column A in src1 will be dropped

[vinieme12]

That's because the script executes top down ,the concatenation never happens before the join

Also I see you posted the same solution as mine , but the join is incorrect it won't be. Left Join as the left table is now to the Right 

It must be a right join

[henrikalmen]

Yes of course, I read the first example wrong when answering you. In the example he joins first, and I assumed that's what he wants to do. But it probably isn't.

[that_anonymous_guy]

thanks @henrikalmen @vinieme12 for your answers....learnt a lot through your healthy discussion 👍