Left Join and Concatenate

[tan_chungkam]

I have 4 tables:

Table1:

MainOrder,

SalesAmount

Table2:

MainOrder,

BuildingType,

Cost

Table3:

MainOrder,

BuildingType,

Cost

Table4:

MainOrder,

BuildingType,

Cost

Tables consist all the mainorder from table2 to table4.

How could I combine these table into 1 table?

[agni_gold]

Go for Concatenate, and for data filtration purpose you can create one flag for each table while concatenating

[undergrinder]

Hi Chung,

I assume the MainOrder in every table is distinct, so I reccommend the following logic:

table_tmp:

Load

MainOrder,

BuildingType,

Cost,

null() as SalesAmount

From table1;

Load

MainOrder,

BuildingType,

Cost,

null() as SalesAmount

From table2;

Load

MainOrder,

null() as Cost

SalesAmount

From table3;

table_final:

Load

MainOrder,

BuildingType,

Cost,

Sum(SalesAmount)

resident table_tmp

group by

MainOrder,

BuildingType,

Cost;

So concatenate all tables, as Agnivesh Kumar suggested, and create a sum of SalesAmount to filter out the possible duplicates.

G.

[Anil_Babu_Samineni]

These tables do concatenate default. Just load and see in Model Viewer. Are there any Synthetic Keys formed?