1 Reply Latest reply: Jan 8, 2016 1:56 PM by Gysbert Wassenaar RSS

    to Link 2 tables : one field on mutiple possible fields

    Alexandre DAUCHY

      Hello all,

       

      I have a table with fields as follow

      The first table is my main table, with customer ID, references, and different hierarchy levels.


      Table A:

      HIE1 HIE2 HIE3 HIE4 HIE5 CUSTOMER REFERENCE

         A     A1    A2    A3    A4     000100             003

         B     B1    B2    B3    B4     000101             004

       

      And another table with only a single hierarchy field (my key with table A), department field and reference. My main problem, hierarchy field presented here could be possibly joined either with HIE1 or HIE2 or HIE3 on table A.

       

      Table B :  

      HIE  REFERENCE    DEPT

      A1         003               GCY

      B2         004               FRT

       

      Result i'd like to have:

      HIE1 HIE2 HIE3 HIE4 HIE5 CUSTOMER REFERENCE DEPT

         A     A1    A2    A3    A4     000100             003          GCY

         B     B1    B2    B3    B4     000101             004           FRT

       

      I can not find how to easily make a link btw two tables with one field that possibly be in different column.

      in Table B, the hierachy key not indicate the level of hierarchy present in table A.

       

      Is anyone has a solution ? Would be very nice. Hope i was clear enough

        • Re: to Link 2 tables : one field on mutiple possible fields
          Gysbert Wassenaar

          Try this:

           

          mapTableB:

          Mapping LOAD

               HIE & '|' &  REFERENCE as Key,

               DEPT

          FROM

               [Table B]

           

          TableA:

          LOAD

               HIE1,

               HIE2,

               HIE3,

               HIE4,

               HIE5,

               CUSTOMER,

               REFERENCE,

               Applymap('mapTableB', HIE2& '|' &  REFERENCE ,

                    Applymap('mapTableB', HIE3& '|' &  REFERENCE ,

                         Applymap('mapTableB', HIE4& '|' &  REFERENCE ,

                              Applymap('mapTableB', HIE5& '|' &  REFERENCE , Null() )))) as DEPT

          FROM

               [Table A]: