topic Re: Problem with duplicates ! in QlikView

Problem with duplicates !

Sat, 05 Sep 2015 18:28:29 GMT

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);

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);

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

Re: Problem with duplicates !

sasiparupudi1 — Tue, 08 Sep 2015 05:57:13 GMT

please provide a sample application

Re: Problem with duplicates !

guruprem — Tue, 08 Sep 2015 06:36:26 GMT

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

Re: Problem with duplicates !

Tue, 08 Sep 2015 06:50:45 GMT

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

Re: Problem with duplicates !

Tue, 08 Sep 2015 07:36:09 GMT

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