5 Replies Latest reply: Sep 4, 2014 2:39 AM by Benas Guzauskas RSS

    Unique IDs with singular related records

    Benas Guzauskas

      Dear QV developers,

       

       

      I would highly appreciate your advice on the following issue.

       

      Let there is a table with a list of IDs and Names. Due to wrong (misspelled) Names IDs are non-unique. For instance, there are entries like these:

       

      [ID],    [Name]
      11,      "Tom"
      11,      "TOM"
      23,      "Janny"
      23,      "Ja_ny"
      33,      "John"
      43,      "Michael"

       

      The aim is to get a table with unique IDs and singular Names. And it does not matter if grammatically right or wrong name is assigned to a particular ID.

       

      I have found a solution, which deals the issue.

       

      A clip from my code:

       

      [Table1]:
      Load * INLINE [
      11,      "Tom"
      11,      "TOM"
      23,      "Janny"
      23,      "Ja_ny"
      33,      "John"
      43,      "Michael"
      ]
      ;

      [Table2]:
      Load
      Distinct(ID) AS [Dist_ID]
      resident [Table1]
      ;

      For v_i = 0 to (NoOfRows('Table2')-1)
      Let vLine = $(v_i);
      Let vID = Peek('Dist_ID',vLine,'Table2');
      First 1
      LOAD [ID]             AS [NewID],
           
      [Name]         AS [NewName]
      resident [Table1] WHERE ID=$(vID);
      Next v_i

       

      However, if the IDs are non-integers:

       

      [ID],    [Name]
      aa11,    "Tom"
      aa11,    "TOM"
      bb23,    "Janny"
      bb23,    "Ja_ny"
      ba33,    "John"
      ab43,    "Michael"

       

      The code does not work. May you, please, advise me on this issue? Perhaps there exists a better solution, than the one I have found?

       

      Thanks in advance!