4 Replies Latest reply: Sep 8, 2015 3:36 AM by Tommaso Bocchini RSS

    Problem with duplicates !

      Hi all,

      i don't know how to resolve this problem.

      Every day i receive a txt file that contains different fields and 1 field that can contain 'OK' or 'KO'.

      I need to store the row with fields 'KO' to create an archive of the error.

      The following day i will receive the txt file that can contains new error (row with field 'KO') or other row with field 'OK'.

      First: i need to check if the error of the first day has been solved --> i can match row of archive with row in the new file on ID and check if the field with KO become OK and write this information in new field: 'solved' 'unsolved'. the error of the first day can be unsolved on the second day (KO).

      second: i need to archive the new error of the day (KO) and testing them the following day.



      ARCHIVE:   // load of the archive
      LOAD @1 as id,
      @2 as c,
      @3 as flag    .//contains OK or KO
      txt, codepage is 1252, no labels, delimiter is ';', msq);

      eft join (ARCHIVE)                                // match archive with new file
      LOAD @1 as id,

                 @3 as flag_2
      FROM  C:\Users\macair\Desktop\NEW_ERROR.txt]
      (txt, codepage is 1252, explicit labels, delimiter is ';', msq);

      Left join (ARCHIVE)   /// check if the error Has been solved
      load *,
      if (flag<>flag_2, 'SOLVED','UNSOLVED') as control
      Resident ARCHIVE;

      ARCHIVE_UPDATE:  //   MY problem---> i don't know hoW to LOAD ONLY NEW ROW WITH FIELD 'KO' and avoid the row matched                 before with KO.



      load  id,

      from C:\Users\macair\Desktop\NEW_ERROR.txt
      where ?????????
      drop Table webdanni;

      store ARCHIVE_UPDATE