Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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
FROM

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


l
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.

                                   

CONCATENATE

load  id,
          
c,
          
flag_2,

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

store ARCHIVE_UPDATE



PLEASE HELP ME 

4 Replies
sasiparupudi1
Honored Contributor III

Re: Problem with duplicates !

please provide a sample application

guruprem
Contributor III

Re: Problem with duplicates !

you can use match function in where condition to load only row with KO.

Not applicable

Re: Problem with duplicates !

You can use Where condition as Not Exists(Id). If it is not working, provide sample application.

Highlighted
Not applicable

Re: Problem with duplicates !

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....

Many thanks to all! Re help me

Community Browser