topic Re: NOT EXISTS Optimized Load Issue in QlikView

NOT EXISTS Optimized Load Issue

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to change some of our scripts to improve load performance and want to remove any data associated with the current day. I've changed to do the following:

Let vCutoff=floor(now());

CUTOFF:

Load

$(vCutoff) as KPI_Date

AutoGenerate 1

;

TEST:

LOAD KPI_ID,

KPI_Date,

Amount

FROM

[KPI_2016-05*.qvd]

(qvd)

WHERE NOT EXISTS (KPI_Date);

DROP TABLE CUTOFF;

The load is now optimised but it only reads one record from each qvd it's reading in, instead of the entire set of data. When I change to EXISTS it reads in everything from the input qvds related to today, which is what I would expect. Why is it not doing the same for the NOT EXISTS?

Thanks

Mark

Re: NOT EXISTS Optimized Load Issue

marcus_sommer — Mon, 30 May 2016 09:50:48 GMT

Is KPI_Date also as floor(Date) stored? Have you the field KPI_Date already loaded before in any other load-statement?

- Marcus

Re: NOT EXISTS Optimized Load Issue

Anonymous — Mon, 30 May 2016 10:33:17 GMT

Thanks for the reply Marcus.

No, this is the entire script just to test the method will work.

Re: NOT EXISTS Optimized Load Issue

Mon, 30 May 2016 10:46:40 GMT

Try converting both fields to numeric format or both to date format and compare.

Re: NOT EXISTS Optimized Load Issue

sunny_talwar — Mon, 30 May 2016 10:59:37 GMT

How about this:

CUTOFF:

LOAD Today() as KPI_Date_Check

AutoGenerate 1;

TEST:

LOAD KPI_ID,

KPI_Date,

Amount

FROM

[KPI_2016-05*.qvd]

(qvd)

WHERE NOT EXISTS (KPI_Date_Check, Floor(KPI_Date));

DROP TABLE CUTOFF;

Re: NOT EXISTS Optimized Load Issue

marcus_sommer — Mon, 30 May 2016 11:08:33 GMT

In general exists() and not exists() do work - if not then is anything with the included field(s) wrong. Therefore check the values and formats from this field and the variable.

- Marcus

Re: NOT EXISTS Optimized Load Issue

Anonymous — Mon, 30 May 2016 11:50:01 GMT

Thanks Sunny,

Unfortunately that doesn't give an optimized load as I believe the column names have to be identical for the optimization to work

Re: NOT EXISTS Optimized Load Issue

Anonymous — Mon, 30 May 2016 12:01:02 GMT

Thanks Marcus, I don't think this is the case as I've even tried with an integer format of the date (e.g. 20160530) so there can be no confusion with formats and I still get the same error. Also, the EXISTS works ok and picks all records that match from the correct date. Its just the NOT EXISTS that picks one record from each input date/file (the files are split by date)

Re: NOT EXISTS Optimized Load Issue

sunny_talwar — Mon, 30 May 2016 12:03:58 GMT

That's true, I forgot

Re: NOT EXISTS Optimized Load Issue

Anonymous — Mon, 30 May 2016 12:08:01 GMT

Thanks Kiru, please see the answer to Marcus above. The date format matches when performing an EXISTS, but when I change to NOT EXISTS it reads in one record from every file we read in, instead of every record

Re: NOT EXISTS Optimized Load Issue

Kushal_Chawda — Mon, 30 May 2016 12:09:48 GMT

KPI_Date is also Number in KPI_2016-05*.qvd? if not first you need to make it in number, but here $(vCutoff) format should be same as KPI_Date in KPI_2016-05*.qvd

Re: NOT EXISTS Optimized Load Issue

Anonymous — Mon, 30 May 2016 12:15:06 GMT

Thanks Kushal,

Please note the format is ok as when it is EXISTS it pulls through the correct data. Its only when changing to NOT EXISTS that it only pulls through one record from each date/file

Re: NOT EXISTS Optimized Load Issue

Anonymous — Mon, 30 May 2016 12:16:01 GMT

The only thing I could think of is that its connected to the autogenerate 1 which is why its picking 1 record from each file. However, I tried a load inline and had the same issue

Re: NOT EXISTS Optimized Load Issue

swuehl — Mon, 30 May 2016 12:59:08 GMT

The load is now optimised but it only reads one record from each qvd it's reading in, instead of the entire set of data. When I change to EXISTS it reads in everything from the input qvds related to today, which is what I would expect. Why is it not doing the same for the NOT EXISTS?

Thanks

Mark

That's because Exists(FIELD) is comparing the input record value of FIELD with the available symbols of FIELD in the symbol table, which contain all FIELD values loaded so far, including the current LOAD statement.

You could probably create the dates you want to load (all possible dates in the QVD except today()) and then just use a WHERE EXISTS(FIELD)

and still keep the optimized LOAD, or go with Sunny's suggestion and do an un-optimized LOAD.

Re: NOT EXISTS Optimized Load Issue

Anonymous — Mon, 30 May 2016 13:36:59 GMT

Thanks swuehl, that makes perfect sense. I just need to find a way to load the previous dates that won't take longer than the potential savings of an optimised load

Re: NOT EXISTS Optimized Load Issue

swuehl — Mon, 30 May 2016 16:26:46 GMT

Assuming your QVD contain KPI_Dates for May 2016 (just guessing from the file name), you could try something like

DATESTOKEEP:

Load

Makedate(2016, 5, Recno() ) as KPI_Date

AutoGenerate Today() - Monthstart(Today() );

You probably need to make this dynamic, basic idea is to not load all dates from source, but to generate the field values based on your expected values.

Re: NOT EXISTS Optimized Load Issue

Anonymous — Tue, 31 May 2016 07:55:35 GMT

Thanks Swuehl,

What I did in the end was to use the IterNo function as follows. It makes it easy to change the range of dates I want to include:

Let varMinDate=floor('2016-05-01');
Let varMaxDate=floor(now());

TempKPIDate:
LOAD
Date($(varMinDate) + IterNo() - 1) as KPI_Date
AutoGenerate 1 While $(varMinDate) + IterNo() -1 < $(varMaxDate);