Append / Join two tables

[girish_talele]

Dear Experts,

I have two tables i.e. "Order Pool" & "Relation".

In first table list of order is there & in second table relation between Main Material & Sub Material is there.

As a Result, we want the data as shown in Result Table.

I failed to achieve the same using JOIN methods. Please suggest how to map the requirement.

[#2]

Hi Girish ,

                 Here is corrected version , Result data is now showing correct output for Order_Main 4 and 5

[girish_talele]

Dear Experts,

Thanks to all, Almost all solutions are matching the end result.

Let me tried the same in actual scenario, will get back to you soon.

Thanks,

Girish

[#4]

Check this one, results are as you were expecting.

Orders:

LOAD * INLINE

[Ord_Main,Main_Mat,Field1,Field2

1,Mat 1,AAA,aaa

2,Mat 2,BBB,bbb

3,Mat 3,CCC,ccc

4,Mat 4,DDD,ddd

5,Mat 5,EEE,eee

6,Mat 1,FFF,fff

7,Mat 2,GGG,ggg];

JOIN

LOAD Num#(mid(Sub_Mat,9,1),'0') as Ord_Main,

Sub_Mat as Main_Mat,

*;

LOAD *

INLINE

[Main_Mat_Relation,Sub_Mat,Field3,Field4

Mat 1,Sub_Mat 1.1,HHH,hhh

Mat 2,Sub_Mat 1.2,III,iii

Mat 3,Sub_Mat 2.1,JJJ,jjj

Mat 4,Sub_Mat 2.2,KKK,kkk

Mat 5,Sub_Mat 2.3,LLL,lll

Mat 1,Sub_Mat 3.1,MMM,mmm

Mat 2,Sub_Mat 3.2,NNN,nnn];

Result:

NoConcatenate

LOAD

Ord_Main,

Main_Mat,

Sub_Mat,

If(Ord_Main=Peek(Ord_Main),Peek(Field1),Field1) as Field1,

If(Ord_Main=Peek(Ord_Main),Peek(Field1),Field2) as Field2,

Field3,

Field4

Resident

Orders;

Drop Table Orders;

[#5]

Correction to show the materials on Orders that are not on the material tables:

OrderPooltmp:

LOAD * INLINE [

Ord_Main, Main_Mat,Field1,Field2

1, Mat 1, AAA, aaa

2, Mat 2, BBB, bbb

3, Mat 3, CCC, ccc

4, Mat 4, DDD, ddd

5, Mat 5, EEE, eee

6, Mat 1, FFF, fff

7, Mat 2, GGG, ggg

];

left join

LOAD * INLINE [

Main_Mat, Sub_Mat, Field3, Field4

Mat 1, Sub_Mat 1.1, HHH, hhh

Mat 1, Sub_Mat 1.2, III, iii

Mat 2, Sub_Mat 2.1, JJJ, jjj

Mat 2, Sub_Mat 2.2, KKK, kkk

Mat 2, Sub_Mat 2.3, LLL, lll

Mat 3, Sub_Mat 3.1, MMM, mmm

Mat 3, Sub_Mat 3.2, NNN, nnn

];

finalresult:

load Ord_Main,Main_Mat,Field1,Field2 resident OrderPooltmp ;

outer join

load Ord_Main,Sub_Mat as Main_Mat,Field1,Field2,Field3,Field4 resident OrderPooltmp where IsNull(Sub_Mat)=0;

drop table OrderPooltmp;