2 Replies Latest reply: Sep 1, 2015 4:51 AM by Sasidhar Parupudi RSS

    Double Loop? Exist()? some way else?

      Hello,

      I want  to do a matching for  two tables, aiming to create a flag. The 2 tables look like following:

       

      Table 1:

      Board Nr.

      Planned Method

      a1

      A

      a1

      C

      a1

      D

      a2

      A

      a2

      B

      a2

      D

      a2

      F

      a3

      C

      a3

      E

       

      Table 2:

      Appointment Index

      Board Nr.

      Used Method

      Flag

      1

      a1

      C

      1

       

       

      D

       

       

       

      A

       

       

       

      F

       

      2

      a3

      B

      0

       

       

      D

       

      3

      a3

      C

      0

      4

      a2

      A

      0

       

       

      B

       

       

       

      D

       

       

       

      E

       

      5

      a1

      A

      1

       

       

      C

       

       

       

      D

       

      6

      a3

      C

      1

       

       

      E

       

       

      For each board, there are planned methods (see Table 1). However, the actually used methods in each appointment may not be as planned and each appointment must belong to one board (see Table 2). When there is at least one planned method not being used, we mark the flag as 0; when all planned methods are used, mark as 1.  Could anyone help me with this match?  Thank you in advance.

       

      Regards, Luwen

        • Re: Double Loop? Exist()? some way else?
          Alessandro Saccone

          Try with:

           

          load ... resident Table2;

           

          left join

           

          load ...., '1' as flag resident Table1;

           

          let me know

          • Re: Double Loop? Exist()? some way else?
            Sasidhar Parupudi

            Try Like this

            A:

            load [Board Nr.],concat([Planned Method],',') as [Planned Method]

            group by [Board Nr.];

            load * Inline

            [

            Board Nr.,Planned Method

            a1,A

            a1,C

            a1,D

            a2,A

            a2,B

            a2,D

            a2,F

            a3,C

            a3,E

             

            ];

            join(A)

            B:

            load [Appointment Index],[Board Nr.],Concat([Used Method],',') as [Used Method]

            Group by

            [Appointment Index],[Board Nr.];

            load * Inline

            [

            Appointment Index,Board Nr.,Used Method

            1,a1,C

            1,a1,D

            1,a1,A

            1,a1,F

            2,a3,B

            2,a3,D

            3,a3,C

            4,a2,A

            4,a2,B

            4,a2,D

            4,a2,E

            5,a1,A

            5,a1,C

            5,a1,D

            6,a3,C

            6,a3,E

            ];

            NoConcatenate   

            final:

            load *,if(SubStringCount([Used Method],[Planned Method])>0,1,0) as flag Resident A;

             

            drop Table A;

             

            hth

            Sasi