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@1asid, @2asc, @3asflag .//contains OK or KO FROM (txt, codepageis 1252, nolabels, delimiteris ';', msq);
left 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);
Leftjoin (ARCHIVE) /// check if the error Has been solved load *, if (flag<>flag_2, 'SOLVED','UNSOLVED') ascontrol 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.
loadid, c, flag_2, from C:\Users\macair\Desktop\NEW_ERROR.txt where ????????? dropTable webdanni;
Thank you for all the replies!!! It works with where not exists (id).
But now i have new problem!! The following day, after reloaded data from archive and match it with new data of the day to check if problems are solved or unsolved... Left join doesnt write new flag (ok or ko) on the last ko of the previous day.... I think i would need a function like update in sql....