3 Replies Latest reply: Apr 13, 2015 11:23 AM by Paul Steinborn RSS

    Join with multipe OR criterias

    Paul Steinborn

      Dear Community!

       

      I have following table structure

       

      Table Data

      Columns :

      Producer,

      DepthSN


      Table Depth

      Columns :

      Depth_lvl_1,

      Depth_lvl_2,

      Depth_lvl_3,

      ...


      How should we join this tables, so that DepthSN will be related to all levels (Depth_lvl_1 ...).

      So if we select DepthSN and it exits only in Depth_lvl_2 the whole row of table depth with the matched DepthSN will be presented.


      I think it should be something like join  DepthSN = Depth_lvl_1 OR  Depth_lvl_2 OR Depth_lvl_3 ???


      Please help!

        • Re: Join with multipe OR criterias
          anbu cheliyan
          Data:
          Load *,DepthSN As DepthSN_New Inline [
          Producer, DepthSN
          p1,aaa
          p2,bbb
          p3,ccc ];
          
          
          Depth:
          Load *,Pick(IterNo(),Depth_lvl_1,Depth_lvl_2,Depth_lvl_3) As DepthSN_New While IterNo() <= 3;
          Load * Inline [
          Depth_lvl_1,Depth_lvl_2,Depth_lvl_3
          aaa,aaa1,aaa2
          bbb1,bbb,bbb2
          ccc1,ccc2,ccc ];
          
          • Re: Join with multipe OR criterias
            Jonathan Dienst

            Hi

             

            You will need a generic link table, something like this:

             

            Data:

            LOAD

              Id,

              Producer,

              Quantity,

              DepthSN

            Inline [

              Id, Producer, Quantity, DepthSN

              1, AA, 22, L1A

              2, BB, 21, L1B

              2, CC, 23, L1C

              3, DD, 24, L2A

              4, BB, 20, L2B

              5, EE, 26, L3A

              6, BB, 23, L3B

            ];

             

            Depth:

            LOAD

              Ref,

              Depth_lvl_1,

              Depth_lvl_2,

              Depth_lvl_3,

              Depth_lvl_1 & Depth_lvl_2 & Depth_lvl_3 As %Key

            Inline [

              Ref, Depth_lvl_1, Depth_lvl_2, Depth_lvl_3

              1966, L1A, L2A, L3A

              9365, L1B, L2B, L3B

              4422, L1C, L2C, L3C

            ];

             

            GenericLink:

            LOAD Depth_lvl_1,

              Depth_lvl_2,

              Depth_lvl_3,

              %Key,

              Depth_lvl_1 As DepthSN

            Resident Depth;

             

            Concatenate(Link)

            LOAD Depth_lvl_1,

              Depth_lvl_2,

              Depth_lvl_3,

              %Key,

              Depth_lvl_2 As DepthSN

            Resident Depth;

             

            Concatenate(Link)

            LOAD Depth_lvl_1,

              Depth_lvl_2,

              Depth_lvl_3,

              %Key,

              Depth_lvl_3 As DepthSN

            Resident Depth;

             

            DROP Fields Depth_lvl_1,

              Depth_lvl_2,

              Depth_lvl_3

            FROM Depth;

             

            Replace the LOADs for Data and Depth with your actual load statements.

             

            HTH

            Jonathan