NOT EXISTS Optimized Load Issue

[Anonymous]

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

[Kushal_Chawda]

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

[Anonymous]

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

[Anonymous]

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

[swuehl · Accepted solution]

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.

[Anonymous]

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

[swuehl]

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.

[Anonymous]

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