2 Replies Latest reply: May 17, 2012 12:38 PM by einsTeIn.NET RSS

    Avoid duplicated records after join

    einsTeIn.NET

      Hi guys,

       

      I'm facing a problem where I have duplicated records after join of two tables.

       

      I have got two table and I just want to merge the value of one field form one table to the other. Problem is that the field I want to merge is unique, whereas my joining fields could be repeated. I just want the first value for each occurrence.

       

      Let's make an example:

       

      Table1:
      LOAD * inline [
        ID, Company, Code
        1, A, 4711
        2, A, 4711
        3, B, 0815
        4, B, 0815
      ];

       

      DistinctTable1:
      LOAD Distinct
      Company,
      Code
      Resident Table1;

       

      So, DistinctTable1 is fine now. But I also want field ID in this table, but just the first value. So, my table should look like this afterwards:

      ID, Company, Code

      1, A, 4711

      3, B, 0815

       

      I tried to use different Join and Keep statements, but since ID is unique it always gives me the whole Table1.

       

      Any suggestion?