2 Replies Latest reply: Sep 5, 2016 12:14 PM by Peter Cammaert RSS

    Relation issue

    Adam Chen

      Hi,

      I have order master with 3 fields whcih related to same employee master. I don't know how to make the relationship between those two tables. I would like the person1, person2 and person3 to be replaced by proper EMMP_name. How to do it right?

       

        Orders:

      DateNoamounttaxtotalperson1person2person3
      9/1/201609010011005105090112090113090114
      9/2/201690200120010210090112090113090114

       

      Employee:

      Emp_IDEMP_Name
      090112Adam
      090113Bob
      090114Unknow
      090115TEST
        • Re: Relation issue
          Vishwarath Nagaraju

          Try this:

          TABLE1:

          CrossTable(EmpName, Emp_ID, 5)

          LOAD

            *

          INLINE [

          Date, No, amount, tax, total, person1, person2, person3

          9/1/2016, 901001, 100, 5, 105, 90112, 90113, 90114

          9/2/2016, 902001, 200, 10, 210, 90112, 90113, 90114

          ];

           

          Employee:

          LOAD * INLINE [

          Emp_ID, EMP_Name

          090112, Adam

          090113, Bob

          090114, Unknow

          090115, TEST

           

           

          ];

          • Re: Relation issue
            Peter Cammaert

            The most simple one that does what you ask is like this:

             

            MapID2Emp:

            MAPPING LOAD * INLINE [

            Emp_ID, EMP_Name

            090112, Adam

            090113, Bob

            090114, Unknow

            090115, TEST

            ];

             

            Orders:

            LOAD Date, No, amount, tax, total,

                 aplymap('MapID2Emp', person1) AS person1,

                 aplymap('MapID2Emp', person2) AS person2,

                 aplymap('MapID2Emp', person3) AS person3

            INLINE [

            Date, No, amount, tax, total, person1, person2, person3

            9/1/2016, 901001, 100, 5, 105, 90112, 90113, 90114

            9/2/2016, 902001, 200, 10, 210, 90112, 90113, 90114

            ];

             

            You can also create an interface table between Orders and Emplkoyees using a CROSSTABLE LOAD. That way you won't duplicate critical values like amounts. For example:

             

            Orders:

            LOAD *

            INLINE [

            Date, No, amount, tax, total, person1, person2, person3

            9/1/2016, 901001, 100, 5, 105, 90112, 90113, 90114

            9/2/2016, 902001, 200, 10, 210, 90112, 90113, 90114

            ];

             

            Order2Emp:

            CROSSTABLE (Emp_SeqNo, Emp_ID, 1)

            LOAD No, person1, person2, person3

            RESIDENT Orders;

             

            Employee:

            LOAD * INLINE [

            Emp_ID, EMP_Name

            090112, Adam

            090113, Bob

            090114, Unknow

            090115, TEST

            ];

             

            Best,

             

            Peter