5 Replies Latest reply: May 7, 2012 12:46 AM by devang sanghavi RSS

    Multiple lookup

      Hi Please help me on below.

       

      I have two tables and i need to limit the second table based on first table on 2 fileds.

       

      Table A

      Employee Code

      Employee Name

       

      Table B

      Order Number

      Sales_responsible_Employee code

      Delivery_responsible_Employee code

      amount

       

       

      What I need is that table B should only load the rows where either sales responsible or delivery responsible is within a given list of employee names from table A (say 'Marc', 'Peter', 'Mike').

       

      Is it possible to make such a script ? If yes, kindly giude me on how can it be done ?

       

      Many thanks in advance,

       

      Devang.

        • Multiple lookup

          You could try the exists function.

          For instance when loading the second table you could use:

          where exists(Employee Code,Sales_responsible_Employee code)

          • Multiple lookup
            Goran Korsgren

            and if you want to check either table:

             

            where exists(Employee Code,Sales_responsible_Employee code)

            or exists(Employee Code,Delivery_responsible_Employee code)

            • Re: Multiple lookup
              Anosh Nathaniel

              Exist and OR may slow down the script performance. You can use right and outer join to load the required data.

               

              Emp:

              Load EmpCode, EmpName

              from <tablename>;

               

              Order1:

              rightjoin(Emp) load

              Order Number ,

              Sales_responsible_Employee_code as EmpCode,

              Sales_responsible_Employee_code as SREC1,

              Delivery_responsible_Employee_code as DREC1

              amount

              from <Ordertable>;

               

              Order2:

              noconcatenate(Order1)

              rightjoin(Emp)

              load

              Order Number ,

              Sales_responsible_Employee_code as SREC2,

              Delivery_responsible_Employee_code as EmpCode,

              Delivery_responsible_Employee_code as DREC2

              amount

              from <Ordertable>;

               

              //Outer Join on Order Number Column

              Order:

              outer join(Order1) load Order Number, SREC2, EmpCode as EmpCode2, DREC2  from Order2;

               

               

              You can check the performance of both of the script if you data is very huge and then decide.

               

              Hope this help,
              Anosh

              • Multiple lookup

                Thanks guys for all your contributions. I think i will try Gandalf and Anosh's solution and that will do the trick. Once again thanks a ton buddies, you rock :-)