Where Not Exists() not excluding data.

Wed, 30 Sep 2015 15:32:17 GMT

As part of an Incremental load process I am following the pattern outlined by Steve Dark in his excellent Blog post here: http://www.quickintelligence.co.uk/qlikview-incremental-load/

That is, I am loading from a small qvd file extracted from a database, and then loading from a larger qvd file which contains the sum of all previous extracts. If the more recent extract contains an updated row, then I don't want to load that row from the existing file:

NoConcatenate

DataSource:

LOAD *

Resident DataSource_Delta;

// if there is already a qvd file, apend that to the loaded data

if not isnull(QvdCreateTime('DataSource.qvd')) then

Concatenate (DataSource)

Load *

from DataSource.qvd (qvd)

where not Exists($(vPKName));

end if;

// store the loaded period data to the filesystem

Almost all of this is driven by variables in external files - I have replaced variable names to protect the guilty...

However, $(vPKName) is the Primary Key in the data. it is unique in the source database, so the idea is that if a Row exists in the DataSource_Delta.qvd file, it is more recent that the version in the file on disk (DataSource.qvd). Only one occurrence of that value can ever appear in a Delta file.

However, the issue is that the where not exists does not appear to be excluding existing data.

My resultant qvd file contains multiple entries for instances of the Primary Key value.

Could someone suggest if there are issues using dollar expansion to provide the name of the column in the source data in the exists clause, or is there another issue?

Many thanks

Mark

Re: Where Not Exists() not excluding data.

Gysbert_Wassenaar — Thu, 01 Oct 2015 06:12:31 GMT

Perhaps like this:

where not Exists([$(vPKName)]);

Re: Where Not Exists() not excluding data.

tamilarasu — Thu, 01 Oct 2015 07:17:45 GMT

Hi,

Try like below..

Where Not Exists ('$(vPKName)');

Re: Where Not Exists() not excluding data.

Thu, 01 Oct 2015 09:09:53 GMT

It would appear that the where not exists ( $(vPKName)) is actually working perfectly.

It is an error in the previous stage of assembling the Delta fileset, and accepting multiple extracts into a single load.

thanks for your help.

Mark

topic Re: Where Not Exists() not excluding data. in QlikView

Where Not Exists() not excluding data.

Re: Where Not Exists() not excluding data.

Re: Where Not Exists() not excluding data.

Re: Where Not Exists() not excluding data.