2 Replies Latest reply: Oct 10, 2012 2:43 PM by Marcos Herrera RSS

    Joining tables problem

      Hi Dear Qv Community

       

      I have this four tables

       

      Tab1

      IDField A
      1A
      1B
      2C
      2D
      3M
      3N

       

      Tab2

      IDField B
      110
      1100
      220
      23
      310
      35

       

       

      Tab3

      IDField C
      1Mat
      1Col
      2Cuc
      2XTZ
      3Abz
      3MNS

       

       

      Tab4

      IDField D
      10,5
      10,6
      20,33
      20,22
      30,11
      30,35

       

      I Want obtain this Finally Table

       

      IDField AField BField CField D
      1A10Mat0,5
      1B100Col0,6
      2C20Cuc0,33
      2D3XTZ0,22
      3M10Abz0,11
      3N5MNS0,35

       

      I try several ways but could not achieve the goal, this is the result after concatenante tables

       

      IDField AField BField CField D
      1A
      1B
      1 10
      1 100
      1 Mat
      1 Col
      1 0,5
      1 0,6
      2C
      2D
      2 20
      2 3
      2 Cuc
      2 XTZ
      2 0,33
      2 0,22
      3M
      3N
      3 10
      2 5
      2 Abz
      2 MNS
      2 0,11
      2 0,35
        • Re: Joining tables problem

          Your data model is assuming that ID is a primary key but it is not.  That's why any type of join with this data source will not work.

           

          Your primary key seems to be ID and field A.  but that link is not in all tables.

           

          However it does look like the load order dictates the join. so you could do this.

           

           

          JoinedTable:

          Load RowNo() as RowID, ID, Field A resident Tab1;

          join

          load RowNo() as RowID, Field B resident Tab2;

          join

          load RowNo() as RowID, Field C resident Tab3;

          join

          load RowNo() as RowID, Field D resident Tab4;

           

          drop field RowID;