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.

[avinashelite]

HI Girish,

OrderPool:

Load Order_main,

  Main_mat ,

  Field1,

  Field2

from OrderPool;

Join(OrderPool:

Load Order_main,

  Main_mat as Key,

  Field1,

  Field2

from OrderPool;)

Relation:

Load

  Main_mat,

  Sub_Mat,

  Field3,

  Field4

from OrderPool;

Result:

Load Order_main,

  Main_mat as key,

  Field1,

  Field2,

  Field3,

  Field4,

resident OrderPool;

Concatenate(Result)

Load Order_main,

  Sub_Mat as key,

  Field1,

  Field2,

  Field3,

  Field4,

resident OrderPool;

drop table OrderPool;

[vikasmahajan]

Please attach sample or excel files

vikas

[girish_talele]

Dear Avinash,

Simple Left Join is not working it is giving result as given below :-

Dear Vikas,

The qvw is attached herewith.

Thanks for looking into,

Girish

[jagan]

Hi,

Try like this

OrderPool:

LOAD

*

FROM OrderPool;

LEFT JOIN (OrderPool)

LOAD

*

FROM Relation;

Concatenate(OrderPool)

LOAD

*

FROM OrderPool;

Hope this helps you.

Regards,

Jagan.

[girish_talele]

Dear Jagan,

Thanks, but the results are same.

The requirement is to Append the Sub-Materials below the Main Materials as given below :-

(Correction in Main Order 6 & 7 is done which was missing in earlier snap)

[Anonymous]

Check this

[jonathandienst]

Hi

This will do it:

OrderPool:

LOAD Ord_Main,

    Main_Mat,

    Field1,

    Field2

FROM

BOMJoin.xlsx

(ooxml, embedded labels, table is OrderPool);

Right JOIN (OrderPool)

LOAD Main_Mat,

  Sub_Mat,

  Field3,

  Field4

FROM

BOMJoin.xlsx

(ooxml, embedded labels, table is Relation);

Concatenate(OrderPool)

LOAD Ord_Main,

  Main_Mat,

  Field1,

  Field2,

  '' As Sub_Mat,

  '' As Field3,

  '' As Field4

FROM

BOMJoin.xlsx

(ooxml, embedded labels, table is OrderPool);

To merge the Main_Mat and Sub_Mat in a straight or pivot table table, use the calculated dimension:

=If(Len(Sub_Mat) = 0, Main_Mat, Sub_Mat)

HTH

Jonathan

[#9]

Hi Girish,

      Refer the following qvw

[#10]

One right join and One outer join.

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

];

right 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;

drop table OrderPooltmp;