3 Replies Latest reply: May 25, 2016 5:06 AM by Stefan Wühl RSS

    How can I join 2 tables while discarding dupes?

    annick whitfield

      Hello,

       

      I have 2 basic tables which I would like to join. In case of dupes, I would like to keep only 1 occurrence, as illustrated below:

       

      GroupByRegion:
      Load * inline
      [Region,        GroupName,
      France,        North
      Argentina,    South
      Australia,     South
      NewZeland, South
      Germany,    North
      Argentina,    North                                           /* THIS REGION IS A DUPE, I WOULD LIKE TO IGNORE IT */
      ];

       

      Facts:
      Load * inline
      [Nb, Region,
      1,  France
      2,  Australia
      3,  Argentina                                                  
      4,  Germany
      5,  NewZeland
      ];

       

      LEFT JOIN
      LOAD *
      Resident GroupByRegion;


      DROP Table GroupByRegion;

       

      ==>

       

      I obtain this table (with 2 occurrences for Nb 3 , one with Argentina in Group South, and another one with Argentina in Group North):

       

       

         

      whereas I would like to have this table (i.e. 1 occurrence for Nb 3 with Argentina in Group South):

       

       

      It would very much appreciate your help on this. Many thanks. Annick