7 Replies Latest reply: Aug 25, 2011 2:32 AM by sandeepa rout RSS

    Joining two tables

    sandeepa rout

      Hi All,

       

       

       

      TableA:

      orgcode

      orgname

      orgtype

      address

      1

      Tata

      MNC

      xxx

      2

      Birla

      MNC

      yyy

      4

      jindal

      MNC

      zzz

      6

      L&T

      MNC

      aaa

       

      TableB:

      orgcode

      orgname

      orgtype

      address

      1

      Tata

      MNC

      xxx

      2

      Birla

      MNC

      yyy

      3

      RMB

      MNC

      ddd

      4

      Jindal

      MNC

      zzz

      5

      Adani

      MNC

      ccc

      6

      L&T

      MNC

      aaa

      7

      wipro

      MNC

      eee

       

       

       

       

       

       

       

      The output should be:

      tableA.orgcode

      tableB.orgcode

      orgname

      orgtype

      address

      matched

      1

      1

      tata

      MNC

      xxx

      matched

      2

      2

      birla

      MNC

      yyy

      matched

      --

      3

      rmb

      MNC

      ddd

      notmatched

      4

      4

      jindal

      MNC

      zzz

      matched

      --

      5

      adani

      MNC

      ccc

      notmatched

      6

      6

      L&T

      MNC

      aaa

      matched

      --

      7

      wipro

      MNC

      eee

      Not matched

       

       

      Even I have to create a list box called Matched where matched and unmatched option will be there. If I will click on nmatched button it will give only unmatched ones.

       

      Please suggest how to do it...

       

      Thanks

      Sandeepa

        • Joining two tables
          Anand Chouhan

          Hi,

           

          For this two table loads you have to load it conditionally and make a single table and then put on column which check the condition match and Unmatched.

           

          Regards

          Anand

          • Joining two tables
            Sridhar Ethiraj

            To explain bit what Anand has explained. Your code should look like below.

             

            Transaction_Temp:

            Load orgcode,

                    orgcode as orgcode_A,

                    orgname,

                    orgtype,

                    address

            From Table A;

             

            Join

             

            Load orgcode,

                    orgcode as orgcode_B,

                    orgname,

                    orgtype,

                    address

            From Table A;

             

            Transaction:

            NoConcatenate

            Load *,

                    if(Len(orgcode_A) = 0,'UnMatched','Matched') as Matched_Status

            Resident Transaction_Temp;

             

            Drop Table Transaction_Temp;

             

            Above code is an untested code. May need to do tweak if required.

             

            Hope this helps you.

             

            -Sridhar

            • Joining two tables
              Anand Chouhan

              Hi,

               

              You need to load data from the tables some thing like this as discuss

               

              Tab1:

              LOAD orgcode,

                   orgname,

                   orgtype,

                   address

              FROM

              Data.xlsx

              (ooxml, embedded labels, table is Sheet9);

                 

              Concatenate(Tab1)

              LOAD orgcode2,

                   orgname,

                   orgtype,

                   address,

                   If(Exists(orgcode,orgcode2),'Matched','Unmatched') as Match

              FROM

              Data.xlsx

              (ooxml, embedded labels, table is Sheet10);

               

              Regards,

              Anand

              • Re: Joining two tables
                Anand Chouhan

                Hi,

                 

                See the attached sample for clearity it is the sample one.

                which uses the code

                 

                Tab1:

                LOAD orgcode,

                     orgname,

                     orgtype,

                     address

                FROM

                Data.xlsx

                (ooxml, embedded labels, table is Sheet9);

                 

                Concatenate(Tab1)

                LOAD orgcode2,

                     orgname,

                     orgtype,

                     address,

                     If(Exists(orgcode,orgcode2),'Matched','Unmatched') as Match

                 

                 

                FROM

                Data.xlsx

                (ooxml, embedded labels, table is Sheet10);

                 

                 

                Reagrds,

                Anand

                  • Re: Joining two tables
                    sandeepa rout

                    TableA:

                     

                    orgcode

                     

                    orgname

                     

                    Orgtype

                     

                    address

                     

                    1

                     

                    tata

                     

                    Mnc

                     

                    aaa

                     

                    2

                     

                    birla

                     

                    Mnc

                     

                    bbb

                     

                    4

                     

                    jindal

                     

                    Mnc

                     

                    ccc

                     

                     

                    TableB:

                     

                    Personcode

                     

                    title

                     

                    firstname

                     

                    lastname

                     

                    1

                     

                    dr

                     

                    Sandeepa

                     

                    Rout

                     

                    2

                     

                    mr

                     

                    Debashish

                     

                    rout

                     

                    4

                     

                    dr

                     

                    Surekha

                     

                    Nayak

                     

                     

                    TableC:

                     

                    Orgcode

                     

                    Personcode

                     

                    Primaryaddress

                     

                    1

                     

                    1

                     

                    xx

                     

                    2

                     

                    2

                     

                    yy

                     

                    4

                     

                    4

                     

                    zz

                     

                     

                     

                     

                    Table1:

                     

                    Orgcode

                     

                    Orgname

                     

                    Orgtypename

                     

                    Address

                     

                    1

                     

                    Tata

                     

                    Mnc

                     

                    aaa

                     

                    2

                     

                    Birla

                     

                    Mnc

                     

                    bbb

                     

                    3

                     

                    L&T

                     

                    Mnc

                     

                    eee

                     

                     

                    4

                     

                    Jindal

                     

                    Mnc

                     

                    ccc

                     

                    5

                     

                    Adani

                     

                    Mnc

                     

                    ddd

                     

                    6

                     

                    Wipro

                     

                    Mnc

                     

                    fff

                     

                     

                    Table2:

                     

                    Personcode

                     

                    Title

                     

                    Firstname

                     

                    Lastname

                     

                    1

                     

                    dr

                     

                    Sandeepa

                     

                    Rout

                     

                    2

                     

                    mr

                     

                    Debashish

                     

                    Rout

                     

                    3

                     

                    dr

                     

                    Sandeep

                     

                    Sahoo

                     

                    4

                     

                    dr

                     

                    Surekha

                     

                    Nayak

                     

                    5

                     

                    dr

                     

                    Abhimanyu

                     

                    Rout

                     

                    6

                     

                    dr

                     

                    Laxmipriya

                     

                    Nayak

                     

                     

                     

                     

                    Table3:

                     

                    Orgcode

                     

                    Personcode

                     

                    Primaryaddress

                     

                    1

                     

                    1

                     

                    xxx

                     

                    2

                     

                    2

                     

                    yyy

                     

                    3

                     

                    3

                     

                    aaa

                     

                    4

                     

                    4

                     

                    zzz

                     

                    5

                     

                    5

                     

                    bbb

                     

                    6

                     

                    6

                     

                    ccc

                     

                     

                     

                    Output should be:

                     

                     

                     

                     

                    Personcode

                    tableA.orgcode

                    Table1.orgcode

                    tableA.orgname

                    Table1.orgname

                    matched

                    1

                    1

                    1

                    Tata

                    tata

                    matched

                    2

                    2

                    2

                    Birla

                    birla

                    matched

                    3

                    --

                    3

                    --

                    L&T

                    unmatched

                    4

                    4

                    4

                    Jindal

                    jindal

                    mathced

                    5

                    --

                    5

                    --

                    adani

                    unmatched

                    6

                    --

                    6

                    --

                    wipro

                    unmatched

                     

                     

                    It means 1st we have to join tableA, tableb, tablec to one(tabA). Then table1, table2, table3 to one (Tab1).

                     

                    Then we have to compare TabA and Tab1.

                     

                    But We have to compare orgcode against personcode.

                     

                    Also we have to create a listbox as Matched where two options should be displayed. If we will click on matched or unmatched it will display accordingly..

                     

                    Please suggest.