1 Reply Latest reply: Jan 28, 2018 12:06 PM by Bill Markham RSS

    Joining two tables resulting in duplicates

    CALLUM STEVENS

      I'm having difficulty joining table 2 to table 1. I've tried a left join but the final table results in duplicate IDs and I'm not sure why. Table 1 has many more different Codes than Table 2.

       

       

      Table 1: (has more columns than this)

      IDCode
      111
      212
      313
      414
      515
      616
      717
      818
      919
      1020
      1121

       

      Table 2:

      CodeTypeDescription
      11Axyz11
      18Bxyz12
      19Cxyz13
      21Dxyz13

       

       

      Desired resulting table:

      IDCodeTypeDescription
      111A
      212xyz11
      313
      414
      515
      616
      717
      818Bxyz12
      919Cxyz13
      1020
      1121Dxyz14
        • Re: Joining two tables resulting in duplicates
          Bill Markham

          I would use ApplyMap.  Maybe something like this :

           

          MapType :

          Mapping

          LOAD

              Code,

              Type

          FROM [lib://289355]

          (html, codepage is 28591, embedded labels, table is @2);

           

          MapDecription :

          Mapping

          LOAD

              Code,

              Description

          FROM [lib://289355]

          (html, codepage is 28591, embedded labels, table is @2);

           

          Data :

          LOAD

          ApplyMap ( 'MapType' , Code , null() )   as Type ,

              ApplyMap ( 'MapDecription' , Code , null() )   as Description ,

              ID,

              Code

          FROM [lib://289355]

          (html, codepage is 28591, embedded labels, table is @1);



          See attached qvf,