topic Re: concatenate where not exist in QlikView

concatenate where not exist

alec1982 — Mon, 21 Sep 2015 09:12:06 GMT

hi,

I am loading data from multiple files and sometimes these files are duplicates due to weekend. the identifier to determine if a file is duplicate is a date filed.(files are xml files and have date field as report date. the report date is same across all rows in each file. so if two files have the same date then it is a duplicate)

I am trying to exclude duplicates when loading data and store it into the final table. tried to do where not exist but that is giving me only one line of data for duplicated date which is wrong..

anyone knows how to do it?

Re: concatenate where not exist

qlikviewwizard — Mon, 21 Sep 2015 09:24:48 GMT

‌hi,

check I this thread if it will help you.

Incremental Load in Qlikview - Sources

Re: concatenate where not exist

martinpohl — Mon, 21 Sep 2015 09:25:35 GMT

The behavior of QlikView is correct.

The first time your load your datas the not exist-field doesn't exists, the data is loaded.

In the second the the value is existing, so it won't

🙂

So load in the first table where the exist field is in the field as a secound value

load

date,

date as existdate,

other fields....

then use this field in the not extis statement.

load

date,

other fields....

from source where not exists (existdate,date);

Regards

Re: concatenate where not exist

alec1982 — Mon, 21 Sep 2015 09:34:35 GMT

that exclude the date and non of the duplicate files is loaded.

Re: concatenate where not exist

alec1982 — Mon, 21 Sep 2015 09:48:57 GMT

to give better idea.. we receive a file every night. for example,

09-17-2015 we received a file and it has 43 rows and one of the columns is the date identifier with value 09-17-2015.

09-18-2015 we received a file and it has 45 rows and one of the columns is the date identifier with value 09-18-2015.

09-19-2015 we received a file and it has 45 rows and one of the columns is the date identifier with value 09-18-2015.

09-20-2015 we received a file and it has 45 rows and one of the columns is the date identifier with value 09-18-2015.

I am trying to avoid concatenating the data from 19 and 20 because it has the same date identifier.

Re: concatenate where not exist

alec1982 — Mon, 21 Sep 2015 10:36:30 GMT

concatenate distinct made it right for me..