11 Replies Latest reply: Jul 18, 2016 8:25 AM by Sunny Talwar RSS

    Duplicate a table if a field has specific characters

    Mike Hillcoat

      Hi experts,

      Basically, I'm pulling many small files and putting them into 1 large table, making them unique by using FileName and a Field to make a Key.

       

      [Text] is the same for every record in each File. It shows up as either

      A,

      B,

      or AB

       

       

       

      Here's my Script

       

       

      LOAD

           Filename() as [File],

           @1 as [ID],

           @2 as [TEXT],

           Filename()&@2&@1 as [Key]

      FROM

      [H:\*]

      (txt, codepage is 1252, no labels, delimiter is ';', msq);

       

       

       

      Here's Example Data

       

      File1

      ID     TEXT

      1          A

      2          A

      3          A

      4          A

       

      File2

      ID     TEXT

      6          AB

      7          AB

      8          AB

      9          AB

       

      If it has AB in it to split these records into 2. So basically pull the entire file with all A's, then pull it again with all B's.

       

      File2

      ID     TEXT

      6          A

      7          A

      8          A

      9          A

       

      6          B

      7          B

      8          B

      9          B

       

       

      My [Key] file should make sure there are no duplicates in this case, since there can't be duplicate file names.

      Any help would be greatly appreciated. Thanks